Close

Quickly Execute a Folder of SQL Scripts against a SQL Server

Another week and another useful dbatools snippet for you today.  Last week at work I was given a folder of 1,500 scripts – each containing a create table statement. Can you imagine having to open each file in Management Studio to be able to execute it? Thank goodness we have PowerShell and dbatools on our side.

The code for this example is pretty short, but there are a couple of things to point out. 

First, I used Connect-DbaInstance to create a server object to use to run the queries.  This means that we’re efficiently reusing the connection rather than opening a new one for each file we want to execute. 

Second, I’m using the foreach method which takes each script file returned from the Get-ChildItem call, and executes Invoke-DbaQuery.  With this we can use the -File parameter to pass in the sql file and that’s really all we need.  This will loop through each file running the sql scripts.

$SqlInstance = 'mssql1'
$destinationDatabase = 'AdventureWorks2021'
$folderPath = '.\output\AdventureWorks2017'
 
# Create a connection to the server that we will reuse - can use SqlCredential for alternative creds
$sqlInst = Connect-DbaInstance -SqlInstance $SqlInstance
 
(Get-ChildItem $folderPath).Foreach{
    Invoke-DbaQuery -SqlInstance $sqlInst -Database $destinationDatabase -File $psitem.FullName
}

That’s really all we need for this blog post, but in order to set this up for a demo I did use a few other dbatools commands. I’ve posted the script above, along with the setup scripts on my GitHub. This includes creating a new database, scripting out all the tables into individual script files, and ensuring all the schemas and other dependencies were ready in the new database.

Thanks for reading, and hope this is a useful snippet. It sure saved me a lot of time this week.

4 thoughts on “Quickly Execute a Folder of SQL Scripts against a SQL Server

  1. So simple, so powerfull!
    I have two questions:
    1) How can I input several servers in $SqlInstance ?
    2) What if I need to execute sp_who2 on all these instances and retrieve the connections? Does DBATools offers an output-to-CSV out of the box?

    1. Hi there, glad you enjoyed!
      1) There are a few options for passing in multiple servers. You can just change this to an array: `$SqlInstance = ‘mssql1’,’mssql2’`, or you could have a text file and pull in the contents of that with something like `$SqlInstance = Get-Content .\servers.txt`, or check out `Get-DbaRegServer` and you can use a CMS.
      2) PowerShell handles CSV natively so you can use `Export-Csv` once you have your results. It’s also worth checking out the `ImportExcel` module, I used it in this post to get PowerShell objects into great looking reports – https://jesspomfret.com/dbachecks-importexcel/

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: