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

需求描述

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

监控脚本

<#批量执行SQL脚本文件#>
<#===========================================#>
$serverInstance="WUXUEL1" 
$Database="111"
#$userName="sa"
#$password="password01!"
$ScriptPath="C:powershellSQLTest"
$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 $_
   
}

其中涉及到几个参数需要配置：

1、批量文件的根目录路径

2、此脚本支持两种验证方式：用户验证 & Windows验证，根据需要自己确定

执行完成报告如下：

当然，最重要的就是，如果执行过程中，某一个脚本出错的问题解决，其实只需要标示出错误的文件名称和错误的信息就可以。

根据需要，自行调整脚本，调整至正确，然后重新执行此脚本就可以了。 

调度脚本

有时候我们部署一般都安排窗口期，这也就意味着需要在夜间某个定时时间去执行该脚本的部署。

为了解决这个问题，Powershell提供了两种基本的调度方式：

1、通过SQL Server的agent建立相应的Job定时的去执行，以完成部署。

2、通过Windows的计划任务去执行调度。

以上两种方式都比较简单，网上资料很多，大家自行查阅。