Needing to run SQL Scripts can be a day to day task for System Administration and DevOps. If you have dozens of SQL Scripts to run this can quickly become a tedious job. Automating this with a Powershell script is an obvious solution. But running SQL Scripts in Powershell is not as easy as it would seem. There’s 3 options available:
- Executing the SQL Script using the invoke-sqlcmd cmdlet. This cmdlet does exactly as promised but it has a number of well documented issues, including an incorrect timeout. One of my biggest problems with this approach is that it requires SQL Server Management tools to be installed to use it.
- Reading and Executing the SQL Script using .NET Data Classes. Creating the .NET Objects in Powershell to execute scripts works well and only requires the .NET Framework. The biggest downside to this approach is that if you have complex SQL Scripts with multiple GO statements, this approach will fail. So this solution will not be best for every scenario.
- Load the SQL Server Management Objects (SMO) manually in your Powershell script and execute your SQL Scripts using SMO. This requires the SMO DLLs, which you would have to install for Option #1. The difference is that you directly load the DLLs using .NET Reflection without having to install SMO or SQL Management Studio. This option gives us the power of SMO without need anything installed prior to using it. You can execute your SQL Scripts from any machine. I’ll further explain how to implement this option.
Batch Executing SQL Scripts with Powershell
Gathering the SMO DLLs
Before you can start using this script you’re going to need to gather some DLLs from your SQL Server environment. On a machine that has SQL Server Management Studio installed you need to copy the following 7 DLLs from the GAC to a network share.
To copy these DLLs from the GAC, run the following command to disable the default GAC Viewer in Windows Explorer.
New-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Fusion -Name DisableCacheViewer -PropertyType DWORD -Value 1
To restore the default GAC Viewer, running the following command.
Remove-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Fusion -Name DisableCacheViewer
Configure the Script for your Environment
Now setup your Powershell Script. You need to modify the top 3 variables to match your environment. If you prefer you could change these to be script parameters instead of hard coded variables.
$SMOLocation – UNC Path to the location you copied the SMO DLLs to earlier.
$SQLServer – Your SQL Server Name. If your using an instance name use the format “SERVER\INSTANCE”. If your using a non-standard port, use the the format “SERVER,PORT”
$SQLDB – The Database Name
- The script accepts a parameter for the directory containing your SQL Scripts. This can be a network UNC path or local path.
- The SMO DLLs are copied down to your user’s temp directory. If they were already copied, they’re not recopied . This speeds up subsequent runs.
- Each SMO DLL is loaded using Reflection.Assembly.LoadFrom()
- Instantiate an SMO Server object and the associated Database object.
- We recursively search the $ScriptsPath location for *.sql file.
- For each script file found read it into a variable using a StreamReader.
- Execute the variable containing the SQL Script using the Database object from step 4.
- If the SQL returns results, display them.