批量数据库还原

在使用前先加载库文件

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