Below is the snippet for using Power Shell to execute list of SQL scripts under a specific folder. I use this script in Octopus deployment to deploy database changes (* for this particular case, we don’t use Code First therefore we don’t use Migrate.exe)
[code language=”powershell”]
# Connection String
[string] $ConnectionString = "server=MyDBServer;database=MyDatabase;user id=Myuser;password=Mypassword;trusted_connection=true;"
#The folder where all the sql scripts are located
[string] $ScriptPath= "C:\Octopus\Applications\SQL2014UAT\Powershell Deployment\Scripts"
# Go to every single SQL files under the folder
foreach ($sqlFile in Get-ChildItem -Path $ScriptPath -Filter "*.sql" | sort-object)
{
$SQLQuery = Get-Content "$ScriptPath\$sqlFile" -Raw
ExecuteSqlQuery $ConnectionString $SQLQuery
}
# executes multiple lines of SQL query
function ExecuteSqlQuery ($ConnectionString, $SQLQuery) {
# Use GO to separate between commands
$queries = [System.Text.RegularExpressions.Regex]::Split($SQLQuery, "^\s*GO\s*`$", [System.Text.RegularExpressions.RegexOptions]::IgnoreCase -bor [System.Text.RegularExpressions.RegexOptions]::Multiline)
$queries | ForEach-Object {
$q = $_
if ((-not [String]::IsNullOrWhiteSpace($q)) -and ($q.Trim().ToLowerInvariant() -ne "go"))
{
$Connection = New-Object System.Data.SQLClient.SQLConnection
Try
{
$Connection.ConnectionString = $ConnectionString
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $q
$Command.ExecuteNonQuery() | Out-Null
}
Catch
{
echo $_.Exception.GetType().FullName, $_.Exception.Message
}
Finally
{
if ($Connection.State -eq ‘Open’)
{
write-Host "Closing Connection…"
$Command.Dispose()
$Connection.Close()
}
}
}
}
}
[/code]
Alternatively if you have SMO, PS extensions and the snap in then you can use simpler script below. For the pre-requisites for invoke-sqlcmd is here
[code language=”powershell”]
Get-ChildItem -Path "C:\Octopus\Applications\SQL2014UAT\Powershell Deployment\Scripts" -Filter "*.sql" | % {invoke-sqlcmd -InputFile $_.FullName}
[/code]
