批量还原SQLServer数据库的PowerShell应用
本文介绍了一个利用PowerShell实现批量还原SQLServer2005/2008数据库的应用，包括使用Microsoft.SqlServer.Management.Smo.Restore类、系统数据DataTable类和PowerShell函数等功能，详细阐述了还原过程中的关键步骤和注意事项。
开始
直接切入主题，今天我们继续来描述PowerShell的一个应用，实现批量还原SQL Server数据库,可以支持SQL Server 2005\2008数据库。在本章中，我们将涉及到几个要点：
- Microsoft.SqlServer.Management.Smo.Restore类
- System.Data.DataTable类
- PowerShell中的函数(Function)
- PowerShell命令Get-Unique
Microsoft.SqlServer.Management.Smo.Restore类
在PowerShell要实现还原SQL Server数据库，我们需要应用到Microsoft.SqlServer.Management.Smo.Restore类。它为我们提供了丰富的还原数据库过程需要的各种属性和方法。首先，提供一份数据库备份文件，我们要还原它，我们需要知道备份文件的标头信息(backup header information),和备份文件里组成(数据文件&日志文件)(the database and log files contained in the backup set)
要是在SQL Server Management Studio(SSMS)里，直接可以调用Transact-SQL:
use master
go
Restore Headeronly From Disk='E:\DBBackup\myDB2008_20111107_01.trn'
Restore filelistonly From Disk='E:\DBBackup\myDB2008_20111107_01.trn'
在执行结果的第一个记录集中，我们可以找到对当前还原有用的信息，如DatabaseName,DackupType,Position,BackupStartDate。
第二个记录集，可以找出数据库myDB2008由两个数据文件”myDB2008” & “myDB20082”和一个日志文件”myDB2008_log”组成。还描述有各文件的物理存储位置，文件类型，文件ID等信息。
要是我们想在PowerShell中通过Restore类获得类似信息，需要通过Restore类提供的两个方法ReadBackupHeader 和 ReadFileList .这两个方法都是会返回一个DataTable对象。 存在着备份文件的标头信息和文件组成信息。
e.g.
$serverInstance="WINSERVER01\SQL2008DE01"
$userName="sa"
$password="sql20081"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
$ServerConnection =new-object "Microsoft.SqlServer.Management.Common.ServerConnection" $serverInstance,$userName, $password
$Server=new-object "Microsoft.SqlServer.Management.Smo.Server" $ServerConnection
$Restore=new-object "Microsoft.SqlServer.Management.Smo.Restore"
$Restore.Devices.AddDevice("E:\DBBackup\myDB2008_20111107_01.trn", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$Read=$Restore.ReadBackupHeader($Server)
$FileRead=$Restore.ReadFileList($Server)
$Read | Format-Table -AutoSize -Wrap -Property DatabaseName,BackupType,BackupStartDate,Position
$FileRead | Format-Table -AutoSize -Wrap -Property LogicalName,PhysicalName,Type,FileId
我们在代码中看到有一行：”$Restore.Devices.AddDevice("E:\DBBackup\myDB2008_20111107_01.trn", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)”描述添加备份文件到当前的还原对象$Restore中，这样我们才可以使用方法ReadBackupHeader 和 ReadFileList读取对应的信息。如果一个文件里面含有多个备份，当我们只需要读取某一个备份文件的标头信息、文件组成信息，就要先设置$Restore的属性FileNumber。系统预设属性FileNumber为0，方法ReadBackupHeader会读取所有文件，方法ReadFileList会默认读取第1个备份的文件组成内容。
e.g.
基本了解方法Restore类中的方法ReadBackupHeader 和 ReadFileList，我们接下来的就要实现如何还原数据库，在Restore类提供有一个还原数据库的方法SqlRestore。
e.g.
$serverInstance="WINSERVER01\SQL2008DE01"
$userName="sa"
$password="sql20081"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
$ServerConnection =new-object "Microsoft.SqlServer.Management.Common.ServerConnection" $serverInstance,$userName, $password
$Server=new-object "Microsoft.SqlServer.Management.Smo.Server" $ServerConnection
$Restore=new-object "Microsoft.SqlServer.Management.Smo.Restore"
$Restore.Devices.AddDevice("E:\DBBackup\test.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$Restore.Database="test"
$Restore.FileNumber=1
$Restore.ReplaceDatabase=$true
$Restore.Script($Server)
$Restore.SqlRestore($Server)
调用方法SqlRestore的时候，我们需要先设置对象$Restore的一些属性，如，
$Restore.Database="test" #数据库名
$Restore.FileNumber=1 #备份文件编号，当一个备份文件包含多个备份的时候，需要设置哪一个文件编号，不然系统会默认还原文件编号为1的备份。
$Restore.ReplaceDatabase=$true #是否覆盖现有数据库
代码$Restore.Script($Server)，只是显示出还原的Transact-SQL语句。当然还有其他的属性，如
$Restore.Action 描述还原的是数据库还是日志，默认是数据库.
$Restore.KeepReplication 描述是否保留复制设置。默认保留。
$Restore.NoRecovery 描述指定不发生回滚。从而使前滚按顺序在下一条语句中继续进行。如，当还原(完整+差异) 或还原(完整+事务日志)，需要设置。
有些时候我们还原数据库，碰到一些应用程序正在使用要还原的数据库。那么我们需要先终止对应的进程，不然还原会报错。在Restore类没有提供终止进程的方法，需要借助Server类的方法KillAllProcesses来删除某一数据库的所有进程。
e.g.
$Server.KillAllProcesses($Restore.Database)
System.Data.DataTable类
在前面我們說到Restore类的方法ReadBackupHeader 和 ReadFileList能返回一個DataTable对象，我们在后面的例子中会使用到DataTable对象，来存储备份的标头信息和文件组成信息。
e.g.
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
$ReadBackupHeader=New-Object "System.Data.DataTable"
$ReadBackupHeader.Columns.Add("DatabaseName","String") |Out-Null
$ReadBackupHeader.Columns.Add("BackupType","Int16") |Out-Null
$ReadBackupHeader.Columns.Add("BackupStartDate","DateTime") |Out-Null
$ReadBackupHeader.Columns.Add("BakFile","String") |Out-Null
$ReadBackupHeader.Columns.Add("NoRecovery","Boolean") |Out-Null
$ReadBackupHeader.Columns.Add("Position","Int16") |Out-Null
$ReadFileList=New-Object "System.Data.DataTable"
$ReadFileList.Columns.Add("DatabaseName","String") |Out-Null
$ReadFileList.Columns.Add("LogicalName","String") |Out-Null
$ReadFileList.Columns.Add("PhysicalName","String") |Out-Null
$ReadFileList.Columns.Add("Type","String") |Out-Null
$ReadFileList.Columns.Add("FileId","Int16") |Out-Null
$ReadFileList.Columns.Add("BakFile","String") |Out-Null
$ReadFileList.Columns.Add("FileNumber","Int16") |Out-Null
定义$ReadBackupHeaderd对象來存储Restore类的方法ReadBackupHeader返回的结果集。定义$ReadFileList对象來存储Restore类的方法ReadFileList返回的结果集。后面的实际例子我们还将应用到增加和删除行的方法.
#增加行
$newRow=$ReadBackupHeader.NewRow()
$newRow["DatabaseName"]="myDB"
$newRow["BackupType"]=2
$newRow["BackupStartDate"]="2011-11-8"
$newRow["BakFile"]=""
$newRow["NoRecovery"]=$true
$newRow["Position"]=1
$ReadBackupHeader.Rows.Add($newRow)
$ReadBackupHeader.AcceptChanges()
#显示行
$ReadBackupHeader | Format-Table -AutoSize -Wrap
#刪除行
$ReadBackupHeader.Rows[0].Delete()
$ReadBackupHeader.AcceptChanges()
#显示行
$ReadBackupHeader | Format-Table -AutoSize -Wrap
代码中有两行“$ReadBackupHeader.AcceptChanges()”描述结束编辑状态。
提示，引用MSDN对AcceptChanges方法的描述：在调用 AcceptChanges 时，EndEdit 方法被隐式调用，以便终止任何编辑。 如果行的 RowState 原来是“Added”或“Modified”，则 RowState 将变成“Unchanged”。 如果 RowState 是“删除”，则该行将被移除。
PowerShell中的函数(Function)
在本章中將應用到PowerShell中的Function。这里将应用到两种用法：
function <名称> {
begin {<处理语句列表>}
process {<处理语句列表>}
end {<处理语句列表>}
}
e.g.
$DBList="DBA,DBB,DBC"
Function CheckDB
{
Param([String]$DBvar)
Begin
{
[Boolean]$CheckResult=$false
}
Process
{
If($DBList -eq "")
{
$CheckResult=$true
}
Else
{
Foreach($x In $DBList.Split(","))
{
If($x -eq $DBvar)
{
$CheckResult=$true
Break
}
}
}
}
End
{
Return $CheckResult
}
}
CheckDB 'A'
函数checkDB，主要是检查输入的$DBvar 是否在清单$DBList中。存在的时候返回True，不存在的時候返回False,有一種特殊的需求就是當$DBList為””的時候。后面的实际例子将会应用到这个函数。
函数的另外一种简单应用就是，不含Begin{},Process{},End{}部份，如
function <name> {
param ([type]$parameter1[,[type]$parameter2])
<statement list>
}
Function fn_UpdateRow
{
Param([string]$DatabaseName,[int32]$row)
$ReadBackupHeader.Rows[$row]["DatabaseName"]=$DatabaseName
$ReadBackupHeader.AcceptChanges()
}
$ReadBackupHeader | Format-Table -AutoSize -Wrap
fn_UpdateRow -DatabaseName "Test" -row 0
$ReadBackupHeader | Format-Table -AutoSize -Wrap
这里定义一函数fn_UpdateRow实现更新DataTable对象$ReadBackupHeader中的”DatabaseName”值。这里函数只作为处理过程，不返回任何值。
PowerShell命令Get-Unique
前面，我们使用DataTable对象$ReadBackupHeader暂存备份的标头信息，当我们要还原一个目录下面的所有备份文件，会把各个数据库备份的标头信息，暂存至$ReadBackupHeader中，再遍历各个数据库进行还原。这里遍历数据库过程我们将应用到命令Get-Unique，过滤掉重复的数据库名称。
e.g.
#遍历数据库
Foreach( $db In $(Foreach($Row In $ReadBackupHeader.rows){$Row["DatabaseName"]}) | Sort-Object | Get-Unique )
{
#还原数据库过程
}
其中“$(Foreach($Row In $ReadBackupHeader.rows){$Row["DatabaseName"]})”返回的是一个Array对象。
实际例子
View Code
# #还原数据库
$serverInstance= " WINSERVER01\SQL2008DE01 "
$userName= " sa "
$password= " sql20081 "
$Path= " E:\DBBackup\ "
$DBList= ""
$RestorePath= " E:\DATA\SQL2008DE01\ "
$StopAt= " 2011-11-8 18:28:00 " # 还原到至时间点 [DateTime]
< # ===========================================#>
[System.Reflection.Assembly]::LoadWithPartialName( " Microsoft.SqlServer.Smo ") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName( " Microsoft.SqlServer.ConnectionInfo ") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName( " Microsoft.SqlServer.SmoExtended ") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName( " System.Data ") | Out-Null
$ServerConnection =new-object " Microsoft.SqlServer.Management.Common.ServerConnection " $serverInstance, $userName, $password
$ReadBackupHeader=New-Object " System.Data.DataTable "
$ReadBackupHeader.Columns.Add( " DatabaseName ", " String ") |Out-Null
$ReadBackupHeader.Columns.Add( " BackupType ", " Int16 ") |Out-Null
$ReadBackupHeader.Columns.Add( " BackupStartDate ", " DateTime ") |Out-Null
$ReadBackupHeader.Columns.Add( " BakFile ", " String ") |Out-Null
$ReadBackupHeader.Columns.Add( " NoRecovery ", " Boolean ") |Out-Null
$ReadBackupHeader.Columns.Add( " Position ", " Int16 ") |Out-Null
$ReadFileList=New-Object " System.Data.DataTable "
$ReadFileList.Columns.Add( " DatabaseName ", " String ") |Out-Null
$ReadFileList.Columns.Add( " LogicalName ", " String ") |Out-Null
$ReadFileList.Columns.Add( " PhysicalName ", " String ") |Out-Null
$ReadFileList.Columns.Add( " Type ", " String ") |Out-Null
$ReadFileList.Columns.Add( " FileId ", " Int16 ") |Out-Null
$ReadFileList.Columns.Add( " BakFile ", " String ") |Out-Null
$ReadFileList.Columns.Add( " FileNumber ", " Int16 ") |Out-Null
# --------------------------------------
# 检查备份数据库清单
# --------------------------------------
Function CheckDB
{
Param([String] $DBvar)
Begin
{
[Boolean] $CheckResult= $false
}
Process
{
If( $DBList -eq "")
{
$CheckResult= $true
}
Else
{
Foreach( $x In $DBList.Split( " , "))
{
If( $x -eq $DBvar)
{
$CheckResult= $true
Break
}
}
}
}
End
{
Return $CheckResult
}
}
# --------------------------------------
# 还原数据库函数
# --------------------------------------
Function RestoreDB
{
begin
{
[Boolean] $ExecResult= $False
}
Process
{
$rowsBackupHeader= $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' ", " BackupStartDate Asc ")
If ( $rowsBackupHeader)
{
Foreach( $rowBackupHeader In $rowsBackupHeader)
{
$File= $rowBackupHeader[ " BakFile "]
$BackupType= $rowBackupHeader[ " BackupType "]
$rowsFileList= $ReadFileList.Select( " BakFile=' "+ $File+ " ' And FileNumber= "+ $rowBackupHeader[ " Position "])
$Restore.Devices.Clear()
$Restore.RelocateFiles.Clear()
$Restore.Database= $db
$Restore.Devices.AddDevice( $File, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$Restore.ReplaceDatabase= $true
$Restore.KeepReplication= $False # 不保留同步设置
$Restore.NoRecovery= $rowBackupHeader[ " NoRecovery "]
$Restore.FileNumber= $rowBackupHeader[ " Position "]
If ( $BackupType -eq 2 ) # 事务日志
{
$Restore.Action= " Log "
$Restore.ToPointInTime=$( if( $StopAt -gt $rowBackupHeader[ " BackupStartDate "]){ $null} Else { $StopAt})
}
Else
{
$Restore.Action= " Database "
$Restore.ToPointInTime= $null
}
foreach( $rowFileList In $rowsFileList)
{
$logicalFileName= $rowFileList[ " LogicalName "]
If ( $rowFileList.Type -eq " D ")
{
$physicalFileName= $RestorePath+ $logicalFileName+ " .mdf "
}
ElseIf ( $rowFileList.Type -eq " L ")
{
$physicalFileName= $RestorePath+ $logicalFileName+ " .ldf "
}
$Restore.RelocateFiles.Add((New-Object " Microsoft.SqlServer.Management.Smo.RelocateFile " $logicalFileName, $physicalFileName)) |Out-Null
}
# 清除正在当前数据库的进程
$Server.KillAllProcesses( $db)
# 执行还原动作
$Restore.SqlRestore( $Server)
$Restore.Wait()
Write-Host " 已还原数据库. DataBase: " $DB " `t`.FileNumber= " $Restore.FileNumber " `tBakFile: " $File
$ExecResult= $True
}
}
Else
{
Write-Warning " 在数据库 $DB .没有可还原的备份文件。 "
}
}
End
{
Return $ExecResult
}
}
# --------------------------------------
# 过滤备份文件
# --------------------------------------
Function FilterDB
{
# 完整
$tmpRows1= $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' And BackupType=1 And BackupStartDate<=' "+ $StopAt+ " ' ", " BackupStartDate Desc ")
[DateTime] $tmpDate= " 1900-01-01 "
if ( $tmpRows1)
{
$tmpRow= $tmpRows1[0]
$tmpDate= $tmpRow[ " BackupStartDate "]
Foreach( $row In $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' And BackupType=1 And BakFile<>' "+ $tmpRow[ " BakFile "]+ " ' "))
{
$row.Delete()
}
}
Else
{
Foreach( $row In $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' And BackupType=1 "))
{
$row.Delete()
}
}
# 差异
$tmpRows5= $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' And BackupType=5 And BackupStartDate<=' "+ $StopAt+ " ' And BackupStartDate>' "+ $tmpDate+ " ' ", " BackupStartDate Desc ")
if ( $tmpRows1 -and $tmpRows5)
{
$tmpRow= $tmpRows5[0]
Foreach( $row In $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' And BackupType=5 And BakFile<>' "+ $tmpRow[ " BakFile "]+ " ' "))
{
$row.Delete()
}
}
Else
{
Foreach( $row In $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' And BackupType=5 "))
{
$row.Delete()
}
}
# 事务日志
$tmpRows2= $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' And BackupType=2 And BackupStartDate>=' "+ $StopAt+ " ' ", " BackupStartDate Asc ")
if ( $tmpRows1 -and $tmpRows2)
{
$tmpRow= $tmpRows2[0]
Foreach( $row In $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' And BackupType=2 And BackupStartDate>=' "+ $StopAt+ " ' And BakFile<>' "+ $tmpRow[ " BakFile "]+ " ' "))
{
$row.Delete()
}
} ElseIf( $tmpRows1)
{
Foreach( $row In $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' And BackupType=2 And BackupStartDate<=' "+ $tmpDate+ " ' "))
{
$row.Delete()
# $ReadBackupHeader | Format-Table -AutoSize -Wrap
}
}
Else
{
Foreach( $row In $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' And BackupType=2 "))
{
$row.Delete()
}
}
# 结束对DataTable的编辑
$ReadBackupHeader.AcceptChanges()
# 设置NoRecovery
$tmpRows= $ReadBackupHeader.Select( " DatabaseName=' "+ $db+ " ' ", " BackupStartDate Desc ")
If( $tmpRows)
{ $tmpRows[0][ " NoRecovery "]= $false}
}
# --------------------------------------
Try
{
$ServerConnection.Connect()
}
Catch
{
Write-Error $_
}
if( $ServerConnection.IsOpen)
{
Try
{
$Server=new-object " Microsoft.SqlServer.Management.Smo.Server " $ServerConnection
$Restore=new-object " Microsoft.SqlServer.Management.Smo.Restore "
$StopAt=$( If( $StopAt -eq ""){ " 3000-12-31 "} Else{[DateTime] $StopAt})
$Path= $Path+$( If($( $Path.Split( " \ "))[-1] -eq "" ){ ""} Else { " \ "})
$RestorePath=$( If( $RestorePath -eq ""){ $Server.Settings.DefaultFile} Else{ $RestorePath}) # 数据库文件默认路径(数据&日志文件同一路径)
$RestorePath= $RestorePath+$( If($( $RestorePath.Split( " \ "))[-1] -eq "" ){ ""} Else { " \ "})
Foreach( $Bak In Get-ChildItem -Path $Path | Where-Object -FilterScript { $_.Mode -eq " -a--- "}) # 遍历备份文件夹
{
$File= $Path+ $Bak.Name
$Restore.Devices.Clear()
$Restore.Devices.AddDevice( $File, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$Restore.FileNumber=0
$Read= $Restore.ReadBackupHeader( $Server)
Foreach ( $row In $Read)
{
$newRow = $ReadBackupHeader.NewRow();
$newRow[ " DatabaseName "]= $row[ " DatabaseName "]
$newRow[ " BackupType "]= $row[ " BackupType "]
$newRow[ " BackupStartDate "]= $row[ " BackupStartDate "]
$newRow[ " BakFile "]= $File
$newRow[ " NoRecovery "]= $true
$newRow[ " Position "]= $row[ " Position "]
$ReadBackupHeader.Rows.Add( $newRow)
$DBName= $row[ " DatabaseName "]
$Restore.FileNumber= $row[ " Position "]
$FileRead= $Restore.ReadFileList( $Server)
Foreach ( $Filerow In $FileRead)
{
$FileNewRow = $ReadFileList.NewRow();
$FileNewRow[ " DatabaseName "]= $DBName
$FileNewRow[ " LogicalName "]= $Filerow[ " LogicalName "]
$FileNewRow[ " PhysicalName "]= $Filerow[ " PhysicalName "]
$FileNewRow[ " Type "]= $Filerow[ " Type "]
$FileNewRow[ " FileId "]= $Filerow[ " FileId "]
$FileNewRow[ " BakFile "]= $File
$FileNewRow[ " FileNumber "]= $Restore.FileNumber
$ReadFileList.Rows.Add( $FileNewRow)
}
}
}
# 结束对DataTable的编辑
$ReadBackupHeader.AcceptChanges()
$ReadFileList.AcceptChanges()
# 遍历数据库
Foreach( $db In $( Foreach( $Row In $ReadBackupHeader.rows){ $Row[ " DatabaseName "]}) | Sort-Object | Get-Unique )
{
If(CheckDB( $db) -eq $true) # 检查备份数据库清单
{
# 过滤备份
FilterDB
# 还原数据库
$Result=RestoreDB
}
}
}
Catch
{
Write-Error $_
}
}
上面脚本能实现的是，在一个目录下存在着各个数据库的完整、差异、事务日志备份文件，可以根据设置“还原至时间点”还原某一个时间点的数据库。支持一个备份文件包含多个备份，支持自定义还原数据库清单。以上的脚本在PowerShell 2.0 + SQL Server2005+WinXP 和PowerShell 2.0 + SQL Server2008 R2+Windows Server 2008 R2 測試通過。
小結
本章我们描述了PowerShell2.0的一个应用，批量还原数据库，支持完整、差异、事务日志备份文件，并对其中的一些知识要点提取出来说明，让我们对PowerShell 2.0 有进一步的了解。在真实中，一些时候，特别是要在数据库后台批量处理一些任务，我们发觉PowerShell能为我们带来许多的方便。当然了，这里只是取1个批量的还原的例子，在实际的应用中，我们也许会碰到比当前例子更为复杂的情况，如页面还原、段落还原、联机还原等。可根据特定的场景，修改或编写更为复杂的脚本。
扫一扫
31
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
