批量数据库还原

在使用前先加载库文件

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

 

}