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

<#===========================================#>
##还原数据库


$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 $_
    }

}