Quickly execute a folder of SQL scripts using dbatools and PowerShell

I have often run into a situation where it’s been necessary to execute a bunch of scripts handed to me by developers or others. When there are only a couple of scripts to run, it’s not a huge problem to just pull them into SSMS and go through them one at a time, but when given a set of 50+ scripts to run, that quickly becomes unwieldy.  I wrote up a really short PowerShell script that uses the dbatools module to get all files in a folder, connect to a server/database and run them.  This might need some tweaking for SQL credentials or to go through all child folders, but should be a good start if you find yourself in a similar situation.  Just change the variables to match your needs and it should be ready to go.

Some things to watch for:

  • You need the “dbatools” module installed. See the link above for installation instructions and much more.
  • If you have batches in a script, this probably won’t work. You need to use the “sqlserver” module and change this to use “Invoke-SqlCmd” instead.
  • This expects write permissions in the folder where you’re running the script as it captures the output of each script to a file
  • There’s no error catching in this snippet. It just attempts to run all SQL files in a given folder.
  • The usual warnings about scripts on the internet apply.

 

That said, here’s the code.  Feel free to comment here or on the gist itself if you have questions or suggestions.


Import-Module dbatools
$scripts = get-childitem "C:\ScriptsToRun\" -Filter *.sql | sort-object Name
$servers = "servername"
$database = "databasename"
foreach ($script in $scripts) {
$OutputFile = $script.directoryname + "\" + $script.basename + ".txt"
Write-Host $OutputFile
Invoke-DbaQuery -SqlInstance $servers -File $script.FullName -Database $database -MessagesToOutput | Out-File -FilePath $OutputFile
}

Comments
  1. Kevin Hill

    Thanks! This looks like exactly what I need for the call I was just on!

    — Kevin H

Leave a Reply

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