批量数据库还原

在使用前先加载库文件

$assemblylist =
"Microsoft.SqlServer.Management.Common",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.Instapi ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.SqlTDiagM ",
"Microsoft.SqlServer.SString ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum",
"Microsoft.SqlServer.Management.Dac",
"Microsoft.SqlServer.Management.DacEnum",
"Microsoft.SqlServer.Management.Utility"

foreach ($asm in $assemblylist)
{
$asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}

# Set SQL Server instance name

$sqlName= "localhost"

 

$backupPath= "e:\zach\"

$destPath = 'c:\SQL Data'

 

$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName

$sqlServer.ConnectionContext .LoginSecure=$false ;

$sqlServer.ConnectionContext .set_Login("sa");

$sqlServer.ConnectionContext .set_Password("jj") 

 

[System.Reflection.Assembly]:: LoadWithPartialName("Microsoft.SqlServer.SMO" ) | Out-Null

[System.Reflection.Assembly]:: LoadWithPartialName("Microsoft.SqlServer.SmoExtended" ) | Out-Null

 

$items =Get-ChildItem $backupPath

foreach ($item in $items )

{

$dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")

$dbRestore.Devices .AddDevice ($item .FullName , "File" )

 

$dt = $dbRestore .ReadFileList ($sqlServer )

$header = $dbRestore .ReadBackupHeader ($sqlServer )

$db=$header .Rows [0].DatabaseName

 

$dbRestore.Database =   $db

$db

       foreach($r in $dt .Rows )

      {

       $p=''

         $dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile" )

       $r. LogicalName;

         $dbRestoreFile.LogicalFileName = $r .LogicalName ;

         $p = $r. PhysicalName.split ("\" )

         $destPath+ "\"+ $p[ $p.length -1]

         $dbRestoreFile.PhysicalFileName = $destPath + "\" +$p[$p.length -1]

         $dbRestore.RelocateFiles .Add ($dbRestoreFile )

      }

 $dbRestore. RelocateFiles

 $sqlServer. KillAllProcesses($db )

       # Call the SqlRestore mathod to complete restore database

       $dbRestore.SqlRestore ($sqlServer )

 

}