SQL Scripts with Powershell

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:

1. 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.
2. 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.
3. 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.

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll
• Microsoft.SqlServer.Batchparser.dll
• Microsoft.SqlServer.BatchParserClient.dll
• Microsoft.SqlServer.SqlEnum.dll
• Microsoft.SqlServer.SqlClrProvider.dll
• Microsoft.SqlServer.Smo.dll

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

Navigate to C:\Windows\assembly\GAC_32\ then into the  Microsoft.SqlServer.BatchParser\10.0.0.0__89845dcd8080cc91 directory and copy Microsoft.SqlServer.Batchparser.dll to your network share location.

Navigate to C:\Windows\assembly\GAC_MSIL\ and copy the remaining DLLs from their respective directories to your network share location.

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

Script Logic

1. The script accepts a parameter for the directory containing your SQL Scripts. This can be a network UNC path or local path.
2. 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.
3. Each SMO DLL is loaded using Reflection.Assembly.LoadFrom()
4. Instantiate an SMO Server object and the associated Database object.
5. We recursively search the $ScriptsPath location for *.sql file.
6. For each script file found read it into a variable using a StreamReader.
7. Execute the variable containing the SQL Script using the Database object from step 4.
8. If the SQL returns results, display them.

param (
   [Parameter(Mandatory=$true)]
   [String]$ScriptsPath
)

$SMOLocation = "\\FileServer\Shared\"
$SQLServer = "MSSQLServer"
$SQLDB = "AdventureWorks"

$SMOLibraries = @("Microsoft.SqlServer.ConnectionInfo.dll", "Microsoft.SqlServer.Management.Sdk.Sfc.dll", "microsoft.sqlserver.batchparser.dll", "Microsoft.SqlServer.BatchParserClient.dll","Microsoft.SqlServer.SqlEnum.dll", "Microsoft.SqlServer.SqlClrProvider.dll", "Microsoft.SqlServer.Smo.dll")

Write-Host ""
Write-Host "          SQL Execution Script                         " -BackgroundColor DarkCyan
Write-Host ""
	
function CopyDLL($dllname)
{
	$DllPath = Join-Path $SMOLocation $dllname 
	$LocalDllPath = Join-Path $env:temp $dllname 

	if (!(Test-Path $DllPath)){
		Write-Error "Could not locate $DllPath. Check if present and rerun"
		exit
	}

	if (!(Test-Path $LocalDllPath)){
		Copy-Item $DllPath $LocalDllPath -Force
		Write-Host "  Downloaded $LocalDllPath"
	}

}

function LoadDLL($dllname)
{
	$LocalDllPath = Join-Path $env:temp $dllname 

	$currentScriptDirectory = Get-Location
	[System.IO.Directory]::SetCurrentDirectory($currentScriptDirectory)
	[Reflection.Assembly]::LoadFrom($LocalDllPath) | Out-Null
	Write-Host "  Loaded $LocalDllPath"

}

# Copy SMO DLLs to $env:temp
Write-Host "Downloading SMO Libraries"
foreach ($dll in $SMOLibraries)
{
	CopyDLL $dll 
}

# Load SMO DLLs from $env:temp
Write-Host "Loading SMO Libraries"
foreach ($dll in $SMOLibraries)
{
	LoadDLL $dll 
}
Write-Host ""

# Create SMO Server Object
$Server = New-Object ('Microsoft.SQLServer.Management.Smo.Server') $SQLServer

# Get SMO DB Object
$db = $Server.Databases[$SQLDB]

# Load All SQL Scripts in Directory
$scripts = Get-ChildItem -Path (Join-Path $scriptspath "*") -Include "*.sql" -Recurse

# Loop through each script and execute
foreach ($SQLScript in $scripts)
{
	$fullpath = $SQLScript.FullName
	
	# Read the Script File into Powershell Memory
	$reader = New-Object System.IO.StreamReader($fullpath)
	$script = $reader.ReadToEnd()

	# Execute SQL
	Write-Host "Executing $SQLScript on $SQLDB...."

	try
	{
    	$ds = $db.ExecuteWithResults($script)
		Foreach ($t in $ds.Tables)
		{
   			Foreach ($r in $t.Rows)
   			{
      			Foreach ($c in $t.Columns)
      			{
          			Write-Host $c.ColumnName "=" $r.Item($c)
      			}
   			}
		}
		Write-Host "Complete"
	}
	catch [Exception]
	{
		Write-Warning "SQL Script Failed"
    	echo $_.Exception|format-list -force
	}	
}

Write-Host "                                                              " -BackgroundColor DarkCyan