SQL ServerPowerShell应用的-批量执行SQL脚本

立刻无异首,我们来兑现PowerShell 2.0于SQL
Server中之一个动,是批量履行SQL脚本。在交接下去的始末,将采用及下面的下令或类库。

  • Sort-Object
  • out-null
  • Write-Error
  • $_
  • System.IO.DirectoryInfo
  • Microsoft.SqlServer.Management.Common.ServerConnection

创立测试环境


 

为还会印证PowerShell脚本的运,我们这边创办个测试环境,模拟一个要提升之SQL脚论文件;首先,要创造两独数据库『TestingDB01』和『TestingDB02』:

 

SQL Server 1SQL Server 2View Code

use master
Go
if db_id(‘TestingDB01’) Is Not null
    Drop Database TestingDB01
Go
Create Database TestingDB01
Go
if db_id(‘TestingDB02’) Is Not null
    Drop Database TestingDB02
Go
Create Database TestingDB02
Go

 

当Microsoft SQL Server Management
Studio(MSSMS)中施行方的创建数据库SQL语句。接下来我们创建三独SQL脚论文件:

  1. 01_ TestingDB_CreateTB&InitializeData.sql

  2. 02_ TestingDB_Procedures_0001.sql

  3. 03_ TestingDB_Procedures_0002.sql

第1独本子,应用为创造数据表和初始化数据应用,第2、3单剧本,只要是储存过程的台本文件,其中起1独存储过程包含有动态的SQL语句,每一个剧本还蕴涵有对数据库『TestingDB01』和『TestingDB02』的台本。这些本子制作是模仿实环境受到之升级换代脚本,列举常见的剧本内容样本。下面是讲述这三只SQL脚论文件的具体内容。

[01_ TestingDB_CreateTB&InitializeData.sql]文件內容:

SQL Server 3SQL Server 4View Code

use TestingDB01
go
if object_id(‘TestingTB1’) Is Not Null
    Drop Table TestingTB1
Go     
Create Table TestingTB1(ID int identity(1,1),Data nvarchar(200))
go
Set identity_insert TestingTB1 On 
Insert into TestingTB1(ID,Data) Values(1,N’Data1′)
Insert into TestingTB1(ID,Data) Values(1,N’Data2′)
Insert into TestingTB1(ID,Data) Values(1,N’Data3′)
Insert into TestingTB1(ID,Data) Values(1,N’Data4′)
Set identity_insert TestingTB1 Off
GO
–TestingDB02
use TestingDB02
go
if object_id(‘TestingTB2’) Is Not Null
    Drop Table TestingTB2
Go     
Create Table TestingTB2(ID int identity(1,1),Data nvarchar(200))
go
Set identity_insert TestingTB2 On 
Insert into TestingTB2(ID,Data) Values(1,N’DB2Data1′)
Insert into TestingTB2(ID,Data) Values(1,N’DB2Data2′)
Insert into TestingTB2(ID,Data) Values(1,N’DB2Data3′)

Set identity_insert TestingTB2 Off
GO

[02_ TestingDB_Procedures_0001.sql]文本內容:

SQL Server 5SQL Server 6View Code

use TestingDB01
GO
if object_id(‘rTestingTB1’) Is Not Null
    Drop proc rTestingTB1
Go     
Create Proc rTestingTB1
As
Select ID,Data From TestingTB1
Go

use TestingDB02
GO
if object_id(‘rTestingTB2’) Is Not Null
    Drop proc rTestingTB2
Go     
Create Proc rTestingTB2
(
    @Columns nvarchar(max)=null,
    @Where nvarchar(max)=null
)
As
If Isnull(@Columns,”)=”
    Set @Columns=’ID,Data’

If Isnull(@Where,”)>”
    Set @Where=’ Where ‘+@Where
Else 
    Set @Where=”
    
Exec(N’Select ‘+@Columns+’ From TestingTB1’+@Where)
Go

 

[03_ TestingDB_Procedures_0002.sql]文本內容:

SQL Server 7SQL Server 8View Code

use TestingDB01
GO
if object_id(‘rTestingTB’) Is Not Null
    Drop proc rTestingTB
Go     
Create Proc rTestingTB
As
Select ID,Data From TestingTB1 Where Not Exists(Select 1 From TestingDB02 Where id=a.id)
Go

 

俺们将地方的老三独SQL脚论文件存储在本机的有平文件路径下,如“E:\ExecuteSQLScript”

SQL Server 9

 

此提醒下,对于当下三独剧本,需依据实际状况,按执行先后顺序,对SQL脚论文件名作编号,如格式“01_…”,”02_…”,”03_…”.

 

 

编写PowerShell脚本


 

当面前我们创建好了测试环境,接下去就是从头编制PowerShell来实现执行SQL脚论文件功能。编写PowerShell脚本的时,我们见面设想这几点问题:

  • 什么连接到SQL Server实例,如何尽SQL脚本.
  • 何以读取SQL脚论文件内容,如何按照SQL脚论号读取.
  • 争处理错误

 

安连接至SQL Server实例,如何实施SQL脚本

PowerShell基于.NET Framework上构建,我们得根据.NET
Framework提供的长的类库实现我们用的效力。这里我们引用到Microsoft.SqlServer.Management.Common命名空間下的ServerConnection类,它好吃咱们总是至某一样SQL
Server实例,而且当ServerConnection类中提供有方法ExecuteNonQuery(),可实施T-SQL语句功能。

e.g.

$serverInstance="WINSERVER01\SQL2008DE01" 
$userName="sa"
$password="sql20081"

[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') |out-null
$ServerConnection =new-object Microsoft.SqlServer.Management.Common.ServerConnection $serverInstance,$userName, $password
$ServerConnection.ExecuteNonQuery(“use test Update myTable Set Data='test' Where ID=3”)

SQL Server 10

 

上面的例子,我们创新数据库test中的表myTable数据,从PowerShell控制台返回的音信”1”可以判断更新脚论早已于实例”WINSERVER01\SQL2008DE01”上执行。当然我们得以以MSSMS上询问证实所更新的数额。

如何读取SQL脚论文件内容,如何随SQL脚论号读取.

念SQL脚论文件,需要获得文件路径下之公文称,把下论文件内容仍文件编号读取出来,把多少易成字符串类型String。这样我们才会调用应用到上的ServerConnection类的ExecuteNonQuery()方法被。这里我们System.IO.DirectoryInfo类来实现。

e.g.

$ScriptPath="E:\ExecuteSQLScript\"
[System.IO.DirectoryInfo]$DirectoryInfo=New-Object System.IO.DirectoryInfo $ScriptPath | Sort-Object
    foreach( $f In ($DirectoryInfo.GetFiles("*.sql"))) 
    {
        $f.Name
    }

SQL Server 11

 

咱下定义1独品类为[System.Text.StringBuilder]的变量$Sql,调用类System.Io.File中的法OpenText(),获取上面三只SQL脚论文件的情,

e.g.

$ScriptPath="E:\ExecuteSQLScript\"
[System.Text.StringBuilder]$Sql=""
[System.IO.DirectoryInfo]$DirectoryInfo=New-Object System.IO.DirectoryInfo $ScriptPath | Sort-Object
    foreach( $f In ($DirectoryInfo.GetFiles("*.sql"))) 
    {
        $Sql=$Sql.AppendLine(([System.Io.File]::OpenText($ScriptPath+$f.Name)).ReadToEnd())
    }

$Sql.ToString()

 

SQL Server 12

 

 

哪些处理错误

俺们无能为力确保我们所勾画的PowerShell脚本完全会正常运作,或以运转中有误,我们需要发一些异常之错误处理,如自定义错误提示等。在PowerShell脚本为我们提供类似C#或SQL
Server 2005\SQL Server 2008的”Try …Catch”用法。

e.g.

Try
{
    $0=0
    $value=1/$0
}
Catch
{
    Write-Error $_
}

 

SQL Server 13

 

 

要考虑的几只问题,我们已相继针对她失去化解了,下面我们描绘成完全的PowerShell脚本,实现批量实践SQL脚论功能:

<#批量执行SQL脚本文件 Andy 2011-10-25 #>
<#===========================================#>
$serverInstance="WINSERVER01\SQL2008DE01" 
$userName="sa"
$password="sql20081"
$ScriptPath="E:\ExecuteSQLScript\"
$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
    try
    {
        $ServerConnection.BeginTransaction()
        Write-Host "BeginTransaction ."

        [System.Text.StringBuilder]$Sql=""
        Foreach($File In $ScriptList.Split(";"))
        {        
            if($File -ne "")
            {
                $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 $_

}

运作脚本结果而图:

SQL Server 14

 

 

提示:

如上脚本测试,使用的SQL Server & Windows环境是:

Microsoft SQL Server 2008 (SP2) – 10.0.4000.0 (Intel X86)
    Sep 16 2010 20:09:22
    Copyright (c) 1988-2008 Microsoft Corporation
    Enterprise Edition on Windows NT 6.0 <X86> (Build 6001:
Service Pack 1)

 

此外在PowerShell 2.0+SQL Server 2005(sp4) + Windows 20003(Windows
XP)上测试通过。

相关文章