最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
PowerShell应用mssql批量还原数据库(支持完整,差异,事务日志)
时间:2022-06-29 08:25:45 编辑:袖梨 来源:一聚教程网
直接切入主题,今天我们继续来描述PowerShell的一个应用,实现批量还原SQL Server数据库,可以支持SQL Server 20052008数据库。在本章中,我们将涉及到几个要点:
- 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:DBBackupmyDB2008_20111107_01.trn' Restore filelistonly From Disk='E:DBBackupmyDB2008_20111107_01.trn' |
在执行结果的第一个记录集中,我们可以找到对当前还原有用的信息,如DatabaseName,DackupType,Position,BackupStartDate。
第二个记录集,可以找出数据库myDB2008由两个数据文件”myDB2008” & “myDB20082”和一个日志文件”myDB2008_log”组成。还描述有各文件的物理存储位置,文件类型,文件ID等信息。
要是我们想在PowerShell中通过Restore类获得类似信息,需要通过Restore类提供的两个方法ReadBackupHeader 和 ReadFileList .这两个方法都是会返回一个DataTable对象。 存在着备份文件的标头信息和文件组成信息。
e.g.
代码如下 | 复制代码 |
$serverInstance="WINSERVER01SQL2008DE01" $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:DBBackupmyDB2008_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:DBBackupmyDB2008_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="WINSERVER01SQL2008DE01" $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:DBBackuptest.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.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 定义$ReadBackupHeaderd对象?泶娲?font color="#000000">Restore类的方法ReadBackupHeader返回的结果集。定义$ReadFileList对象?泶娲?font color="#000000">Restore类的方法ReadFileList返回的结果集。后面的实际例子我们还将应用到增加和删除行的方法.
#增加行 |
代码中有两行“$ReadBackupHeader.AcceptChanges()”描述结束编辑状态。
提示,引用MSDN对AcceptChanges方法的描述:在调用 AcceptChanges 时,EndEdit 方法被隐式调用,以便终止任何编辑。 如果行的 RowState 原来是“Added”或“Modified”,则 RowState 将变成“Unchanged”。 如果 RowState 是“删除”,则该行将被移除。
PowerShell中的函数(Function)
在本章中???玫?owerShell中的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,不存在的?r候返回False,有一?特殊的需求就是?$DBList??rdquo;”的?r候。后面的实际例子将会应用到这个函数。
函数的另外一种简单应用就是,不含Begin{},Process{},End{}部份,如
代码如下 | 复制代码 |
function
param ([type]$parameter1[,[type]$parameter2])
}
Function fn_UpdateRow |
这里定义一函数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对象。
实际例子
代码如下 | 复制代码 |
<#===========================================#>
##还原数据库 $serverInstance="WINSERVER01SQL2008DE01" $userName="sa" $password="sql20081" $Path="E:DBBackup" $DBList="" $RestorePath="E:DATASQL2008DE01" $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
相关文章
- 光遇12.4大蜡烛在哪里 光遇12月4日大蜡烛位置攻略 12-04
- 无限暖暖公测兑换码有哪些 无限暖暖公测版本兑换码介绍 12-04
- 绝区零当三明治来敲门怎么玩 绝区零当三明治来敲门活动介绍 12-04
- 奇迹暖暖绮彩木马搭配攻略 奇迹暖暖绮彩木马过关攻略 12-04
- 光遇12.4每日任务怎么做 光遇12月4日每日任务做法攻略 12-04
- 光遇12.4季节蜡烛在哪里 光遇12月4日季节蜡烛位置攻略 12-04