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.