Code snippets : Execute SQL script using PowerShell

by | Jul 22, 2015 | Automation, Databases, Scripting, Windows | 0 comments

Recently I went through requirement of running SQL scripts on multiple databases on different servers. As the list of databases was different from script to script I decided to create universal solution, which will allow to easily prepare for deployment and can be simply reused at any time.

As a platform to execute scripts I went with PowerShell as that provides flexibility in case additional functionality will be required.

Whole solution contains 3 files:

  • SqlExecutionInflow.csv – file contains list of databases and servers where particular database is located. It is simple CSV file with 2 columns
  • SqlExecQuery.sql – contains SQL script which will be executed against all databases listed in SqlExecutionInflow.csv
  • SqlExec.ps1 – main script which load SqlExecInflow.csv and executes query from SqlExecQuery.sql

All files have to be placed in same folder. As a result script will create transcript file with output from all executed commands.

And here are example files and script itself…

SqlExecInflow.csv

DBname,DBserver
AdventureWorksDB,SQL-SERVER-01
Northwind,SQL-SERVER-01

SqlExecQuery.sql

SELECT @@VERSION

SqlExec.ps1

Import-Module SqlPs

# Load Smo and referenced assemblies.
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo');
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Sdk.Sfc');
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO');
# Requiered for SQL Server 2008 (SMO 10.0).
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended');

# Determine script location for PowerShell
$ScriptDir = Split-Path $script:MyInvocation.MyCommand.Path

$SqlExecInflowList = $ScriptDir + "\SqlExecInflow.csv"
$SqlExecOutputLog = $ScriptDir + "\SqlExecOutput.log"
$SqlExecQueryFile = $ScriptDir + "\SqlExecQuery.sql"

$DatabaseListArray = @()
$DatabaseListArray = Import-CSV $SqlExecInflowList

Start-Transcript $SqlExecOutputLog

foreach ($DatabaseItem in $DatabaseListArray)
{
$DatabaseName = $DatabaseItem.DBname
$DatabaseServer = $DatabaseItem.DBserver

Invoke-SqlCmd -InputFile $SqlExecQueryFile -ServerInstance $DatabaseServer -Database $DatabaseName -Verbose
}

Stop-Transcript

Submit a Comment