SQL Server自动化运维系列——批量执行SQL脚本（Power Shell）

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

需求描述

一般在生产环境中，在投产的情况下，需要批量的来执行SQL脚本文件，来完成整个投产，如果投产文件比较多的情况下，无疑这是一个比较痛苦的过程，所以本篇通过PowerShell脚本来批量完成。

监控脚本

<#批量执行SQL脚本文件#>
<#===========================================#>
$serverInstance="WUXUEL1"
$Database="111"
#$userName="sa"
#$password="password01!"
$ScriptPath="C:\powershell\SQLTest\"
$ScriptList=""
<#===========================================#>
$n="`n"
$r="`r"
While ($ScriptList.IndexOf($n) -gt 0)
{$ScriptList=$ScriptList.Replace($n,";")}
While ($ScriptList.IndexOf($r) -gt 0)
{$ScriptList=$ScriptList.Replace($r,";")}
While ($ScriptList.IndexOf(" ") -gt 0)
{$ScriptList=$ScriptList.Replace(" ","")}
While ($ScriptList.IndexOf(",") -gt 0)
{$ScriptList=$ScriptList.Replace(",","")}
If ($ScriptList.IndexOf(".sql") –le 0)
{
$ScriptList=""
[System.IO.DirectoryInfo]$DirectoryInfo=New-Object System.IO.DirectoryInfo $ScriptPath | Sort-Object
foreach( $f In ($DirectoryInfo.GetFiles("*.sql")))
{
$ScriptList=$ScriptList+";"+$f.Name
}
}
Try
{
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') |out-null
$ServerConnection =new-object Microsoft.SqlServer.Management.Common.ServerConnection #$serverInstance,$userName, $password
$ServerConnection.ConnectionString = "Data Source=$serverInstance;Initial Catalog=$Database;Integrated Security=True"
try
{
$ServerConnection.BeginTransaction()
Write-Host "BeginTransaction ." [System.Text.StringBuilder]$Sql=""
Foreach($File In $ScriptList.Split(";"))
{
if($File -ne "")
{
Write-Host $ScriptPath$File " ...start" $Sql=$Sql.AppendLine(([System.Io.File]::OpenText($ScriptPath+$File)).ReadToEnd())
$ServerConnection.ExecuteNonQuery($Sql)|out-null
$Sql="" Write-Host $ScriptPath$File " ...OK!"
}
}
$ServerConnection.CommitTransaction() Write-Host "CommitTransaction ."
}
Catch
{
If ($ServerConnection.TransactionDepth -gt 0)
{
$ServerConnection.RollBackTransaction()
Write-Host "RollBackTransaction ."
} Write-Error $_
}
}
Catch
{
Write-Error $_}