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.