你的位置:首页 > 数据库

[数据库]批量数据库还原


在使用前先加载库文件

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