{"id":47807,"date":"2021-03-23T19:07:31","date_gmt":"2021-03-23T18:07:31","guid":{"rendered":""},"modified":"2021-03-23T19:07:31","modified_gmt":"2021-03-23T18:07:31","slug":"quickly-execute-a-folder-of-sql-scripts-against-a-sql-server","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-gb\/industry\/blog\/technetuk\/2021\/03\/23\/quickly-execute-a-folder-of-sql-scripts-against-a-sql-server\/","title":{"rendered":"Quickly Execute a Folder of SQL Scripts against a SQL Server"},"content":{"rendered":"

\"An<\/p>\n

Another week and another useful dbatools snippet for you today. Last week at work I was given a folder of 1,500 scripts \u2013 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<\/a> on our side.<\/p>\n

The code for this example is pretty short, but there are a couple of things to point out.<\/p>\n

First, I used\u00a0Connect-DbaInstance<\/code> to create a server object to use to run the queries. This means that we\u2019re efficiently reusing the connection rather than opening a new one for each file we want to execute.<\/p>\n

Second, I\u2019m using the foreach method which takes each script file returned from the Get-ChildItem<\/code>\u00a0call, and executes\u00a0Invoke-DbaQuery<\/code>. With this we can use the -File<\/code> parameter to pass in the SQL file and that\u2019s really all we need. This will loop through each file running the SQL scripts.<\/p>\n

$SqlInstance<\/span> = 'mssql1'<\/span>\r\n$destinationDatabase<\/span> = 'AdventureWorks2021'<\/span>\r\n$folderPath<\/span> = '.\\output\\AdventureWorks2017'<\/span>\r\n  \r\n# Create a connection to the server that we will reuse - can use SqlCredential for alternative creds<\/span>\r\n$sqlInst<\/span> = Connect-DbaInstance<\/span> -SqlInstance<\/span> $SqlInstance<\/span>\r\n  \r\n(Get-ChildItem<\/span> $folderPath<\/span>).Foreach{\r\n    Invoke-DbaQuery<\/span> -SqlInstance<\/span> $sqlInst<\/span> -Database<\/span> $destinationDatabase<\/span> -File<\/span> $psitem<\/span>.FullName\r\n}<\/pre>\n
Thanks for reading, and hope this is a useful snippet. It sure saved me a lot of time this week.<\/p>\n
-=-<\/p>\n
Jess is a Database Engineer with a passion for automation, proper football and fitness. You can follow her on Twitter<\/a>\u00a0and via\u00a0her personal blog<\/a>.<\/em><\/p>\n
 <\/p>\n
Learn more<\/h2>\n
That\u2019s 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\u2019ve posted the script above, along with the setup scripts on my GitHub<\/a>. 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.<\/p>\n