PowerShell Application MSSQL Batch RESTORE database (complete, variance, transaction log)

Directly into the subject, today we continue to describe an application of PowerShell, which implements a batch restore of SQL Server databases and can support SQL Server 20052008 databases. In this chapter, we will cover several points:

• Microsoft.SqlServer.Management.Smo.Restore class
• System.Data.DataTable class
• Functions in PowerShell (function)
• PowerShell command Get-unique

Microsoft.SqlServer.Management.Smo.Restore class

In PowerShell to implement the restore SQL Server database, we need to apply to the Microsoft.SqlServer.Management.Smo.Restore class. It provides us with a wealth of the various properties and methods needed to restore the database process. First, to provide a database backup file that we want to restore, we need to know the header information of the backup file (backup header information), and the composition in the backup file (data file & log file) Contained in the backup set)

In SQL Server Management Studio (SSMS), Transact-SQL can be invoked directly:

  
   
   

	  
   
    
    
 
     
 
      
The code is as follows
 
      
Copy Code
 
     
 
     
 
      
Use master
Go
Restore headeronly from disk= ' e:dbbackupmydb2008_20111107_01.trn '
Restore filelistonly from disk= ' e:dbbackupmydb2008_20111107_01.trn '
 
     
 
    
  
   
   

In the first recordset of the execution result, we can find information that is useful for the current restore, such as databasename,dackuptype,position,backupstartdate.

The second Recordset, you can find that the database myDB2008 consists of two data files "myDB2008" & "myDB20082" and a log file "Mydb2008_log". It also describes the physical storage locations, file types, file IDs, and other information for each file.

If we want to get similar information through the Restore class in PowerShell, we need the two methods provided by the Restore class readbackupheader and readfilelist . Both of these methods return a DataTable object. There are header information and file composition information for the backup file.

e.g.

  
  

	  
   
   
 
    
 
     
The code is as follows
 
     
Copy Code
 
    
 
    
 
     
$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


 
    
 
   
  
  

We see a line in the code: "$Restore. Devices.adddevice (" E:dbbackupmydb2008_20111107_01.trn ", [ Microsoft.sqlserver.management.smo.devicetype]::file) "describes adding backup files to the current restore object $restore so that we can use the method ReadBackupHeader and readfilelist read the corresponding information. If a file contains more than one backup, when we only need to read a backup file header information, file composition information, we must first set the $restore properties FileNumber. The System preset property is filenumber to 0, and method ReadBackupHeader reads all the files, and the method ReadFileList reads the 1th backed up file by default.

e.g.

Basic understanding of methods in the restore class readbackupheader and readfilelist, our next step is to implement how to restore the database, in the restore class provides a way to restore a database sqlrestore.

e.g.

  
  

	  
   
   
 
    
 
     
The code is as follows
 
     
Copy Code
 
    
 
    
 
     
$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)
 
    
 
   
  
  

When calling method SQLRestore , we need to first set some properties of the object $restore, such as,

$Restore. database= "Test" #数据库名
$Restore. Filenumber=1 #备份文件编号, when a backup file contains multiple backups, which file number you need to set, or the system defaults to restore the backup with file number 1.
$Restore. replacedatabase= $true #是否覆盖现有数据库

Code $restore.script ($Server), only displays the restored Transact-SQL statement. Of course, there are other attributes, such as

$Restore. Action describes whether the restore is a database or a log, and the default is a database.

$Restore. Keepreplication describes whether to keep replication settings. Default retention.

$Restore. NoRecovery description Specifies that no rollback occurs. This allows the roll forward to proceed sequentially in the next statement. For example, when restoring (full + difference) or restore (full + transaction log), you need to set up.

Sometimes we restore the database and run into some applications that are using the database to be restored. Then we need to terminate the corresponding process, otherwise the restore will be an error. The restore class does not provide a way to terminate a process, and you need to use the method killallprocesses of the Server class to delete all processes of a database.

e.g.

$Server. Killallprocesses ($Restore. Database)

S ystem. Data.datatable class

In front of me??? Methods to the restore class readbackupheader and readfilelist can return one?? DataTable Object, we use the DataTable object in the following example to store the header information and the file composition information of the backup.

e.g.

  
  

	  
   
   
 
    
 
     
The code is as follows
 
     
Copy Code
 
    
 
    
 
     
 
      
 
       
[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
 
      
 
      
 
       

         Define the $readbackupheaderd object? 泶 wa font color= "#000000" >restore the method ReadBackupHeader The result set returned by the class. Define the $readfilelist object? 泶 wa font color= "#000000" >restore the method readfilelist The result set returned by the class. We will also apply the method of adding and deleting rows in the actual example that follows. 
       
 
      
 
      
 
       
 
        
#增加行
$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

#?h except row
$ReadBackupHeader. Rows[0]. Delete ()
$ReadBackupHeader. AcceptChanges ()

#显示行
$ReadBackupHeader | Format-table-autosize-wrap
 
       
 
      
 
 
    
 
   
  
  

There are two lines in the code $ReadBackupHeader. AcceptChanges () describes the end of the edit state.

Tip , refer to MSDN's description of the AcceptChanges method : when AcceptChangesis invoked, the EndEdit method is implicitly invoked to terminate any edits. If the RowState of the line is originally "Added" or "Modified", RowState will become "unchanged". If RowState is "delete", the row is removed.

PowerShell the functions in (Function)

In this chapter??? The function in Mae Owershell. There are two ways to use this:

Function < name > {

Begin {< Processing Statement list}

Process {< processing Statement list}

End {< processing Statement list}

}

e.g.

 
   
   

	  
  
    
    
 
     
 
      
The code is as follows
 
      
Copy Code
 
     
 
     
 
      
$DBList = "Dba,dbb,dbc"

Function CheckDB
{
Param ([String] $DBvar)


{
[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 '
 
     
 
    
 
   
   

function checkdb, primarily to check whether the input $dbvar is in the manifest $dblist. Returns True when present, does not exist? R waiting to return False, there is a special demand is $DBList? rdquo; " ? r Hou. The actual example that follows will be applied to this function.

Another simple application of a function is that it does not contain begin{},process{},end{} parts, such as

code is as follows	copy code
function <name> { param ([type] $paramete R1[,[type] $parameter 2]) <statement list> } $ReadBackupHeader. AcceptChanges () } $ ReadBackupHeader | Format-table-autosize-wrap Fn_updaterow-databasename "Test"-row 0 $ReadBackupHeader | Format-table-autosize-wrap

This defines a function Fn_updaterow implementation to update the "DatabaseName" value in a DataTable object $readbackupheader. Here the function acts only as a process and does not return any values.

PowerShell command Get-unique

Before we use the DataTable object to $readbackupheader the header information of the staging backup, when we want to restore all the backup files under a directory, the header information of each database backup is staged to $readbackupheader. Then iterate through each database for restoration. This traversal of the database process will be applied to the command get-unique, filtering out duplicate database names.

e.g.

#遍历数据库    
        
  
   
   

	  
   
    
    
 
     
 
      
The code is as follows
 
      
Copy Code
 
     
 
     
 
      
foreach ($db in $ (foreach ($Row in $ReadBackupHeader. rows) {$Row ["DatabaseName"]}) | Sort-object | Get-unique)
{
#还原数据库过程
}
 
     
 
    
  
   
   

Where "$ (Foreach ($Row in $ReadBackupHeader. rows) {$Row [" DatabaseName]}) "returns an Array object.

Practical examples

The code is as follows

Copy 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 the database has been restored. DataBase: "$DB" ' t '. Filenumber= "$Restore. FileNumber" ' Tbakfile: "$File $ExecResult = $True } } Else { Write-warning $DB in the database. There are no backup files to restore. " } } End { Return $ExecResult } } #-------------------------------------- #过滤备份文件 #-------------------------------------- Function Filterdb { #完整 $tmpRows 1= $ReadBackupHeader. Select ("databasename=" + $db + "' and backuptype=1 and backupstartdate<= '" + $StopAt + "") , "Backupstartdate Desc") [DateTime] $tmpDate = "1900-01-01" if ($tmpRows 1) { $tmpRow = $tmpRows 1[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 () } } #差异 $tmpRows 5= $ReadBackupHeader. Select ("databasename=" + $db + "' and backuptype=5 and backupstartdate<= '" + $StopAt + "" and backupstartdate> ' "+ $tmpDate +" "," Backupstartdate Desc ") if ($tmpRows 1-and $tmpRows 5) { $tmpRow = $tmpRows 5[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 () } } #事务日志 $tmpRows 2= $ReadBackupHeader. Select ("databasename=" + $db + "' and backuptype=2 and backupstartdate>= '" + $StopAt + "") , "Backupstartdate ASC") if ($tmpRows 1-and $tmpRows 2) { $tmpRow = $tmpRows 2[0] Foreach ($row in $ReadBackupHeader. Select ("databasename=" + $db + "' and backuptype=2 and backupstartdate>= '" + $StopAt + "' and bakfile<> '" + $tmpRow ["Bakfile"]+ "")) { $row. Delete () } }elseif ($tmpRows 1) { 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}) #数据库文件默认路径 (Data & Log file same path) $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 $_ } }

What the above script can achieve is that there is a complete, differential, transaction log backup file for each database under a directory, and you can restore a database at a point in time based on the "restore to point in time" setting. Supports a backup file that contains multiple backups and supports a custom restore database inventory. The scripts above are in PowerShell 2.0 + SQL SERVER2005+WINXP and PowerShell 2.0 + SQL Server2008 r2+windows Server 2008 R2