ITPub博客

首页 > 数据库 > 数据库开发技术 > PowerShell应用之-批量还原数据库(支持完整,差异,事务日志)

PowerShell应用之-批量还原数据库(支持完整,差异,事务日志)

原创 数据库开发技术 作者:wghao 时间:2011-11-10 07:57:13 0 删除 编辑
今天我们继续来描述PowerShell的一个应用,实现批量还原SQL Server数据库,可以支持SQL Server 20052008数据库。在本章中,我们将涉及到几个要点:
  • Microsoft.SqlServer.Management.Smo.Restore类
  • System.Data.DataTable类
  • PowerShell中的函数(Function)
  • PowerShell命令Get-Unique
[@more@]

开始


直接切入主题,今天我们继续来描述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'

image

在执行结果的第一个记录集中,我们可以找到对当前还原有用的信息,如DatabaseName,DackupType,Position,BackupStartDate。

第二个记录集,可以找出数据库myDB2008由两个数据文件”myDB2008” & “myDB20082”和一个日志文件”myDB2008_log”组成。还描述有各文件的物理存储位置,文件类型,文件ID等信息。

要是我们想在PowerShell中通过Restore类获得类似信息,需要通过Restore类提供的两个方法ReadBackupHeaderReadFileList .这两个方法都是会返回一个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

image

我们在代码中看到有一 行:”$Restore.Devices.AddDevice("E:DBBackupmyDB2008_20111107_01.trn", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)”描述添加备份文件到当前的还原对 象$Restore中,这样我们才可以使用方法ReadBackupHeaderReadFileList读取对应的信息。如果一个文件里面含有多个备份,当我们只需要读取某一个备份文件的标头信息、文件组成信息,就要先设置$Restore的属性FileNumber。系统预设属性FileNumber为0,方法ReadBackupHeader会读取所有文件,方法ReadFileList会默认读取第1个备份的文件组成内容。

e.g.

image

基本了解方法Restore类中的方法ReadBackupHeaderReadFileList,我们接下来的就要实现如何还原数据库,在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)

image

调用方法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)

image

System.Data.DataTable


在前面我們說到Restore类的方法ReadBackupHeaderReadFileList能返回一個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

image

代码中有两行“$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'

image

函数checkDB,主要是检查输入的$DBvar 是否在清单$DBList中。存在的时候返回True,不存在的時候返回False,有一種特殊的需求就是當$DBList為””的時候。后面的实际例子将会应用到这个函数。

函数的另外一种简单应用就是,不含Begin{},Process{},End{}部份,如

function {

param ([type]$parameter1[,[type]$parameter2])

}

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

image

这里定义一函数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="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---"}) #遍历备份文件夹

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7764484/viewspace-1056241/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2007-12-19

  • 博文量
    57
  • 访问量
    233003