使用 Windows Powershell 3.0 還原 SQL Server 數據庫

我正在嘗試使用 PowerShell 腳本恢復 SQL Server 數據庫，但遇到了問題。

這是我得到的錯誤：

使用“1”參數呼叫“SqlRestore”的異常：“伺服器’WUSFK250042-OLU\SQLSERVER2008R2’的恢復失敗。”在第48行字元：1 + $ smoRestore.SqlRestore( $ 伺服器）

這是我的程式碼：

#clear screen
cls

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$backupFile = "C:\SafewayRT\SafewayRTFUll.bak"

#we will query the database name from the backup header later
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "WUSFK250042-OLU\SQLSERVER2008R2"
$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File")
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")

#restore settings
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"
$smoRestorePercentCompleteNotification = 10;
$smoRestore.Devices.Add($backupDevice)

#get database name from backup file
$smoRestoreDetails = $smoRestore.ReadFileList($server)

#display database name
"Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["Safeway_LogixRT"]

#give a new database name
$smoRestore.Database =$smoRestoreDetails.Rows[0]["Safeway_LogixRT"]

#specify new data and log files (mdf and ldf)
$smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$smoRestoreLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")

#the logical file names should be the logical filename stored in the backup media
$smoRestoreFile.LogicalFileName = $smoRestoreDetails.Rows[0]["Safeway_LogixRT"]
$smoRestoreFile.PhysicalFileName = $server.Information.MasterDBPath + "\" + $smoRestore.Database + "_Data.mdf"
$smoRestoreLog.LogicalFileName = $smoRestoreDetails.Rows[0]["Safeway_LogixRT"] + "_Log"
$smoRestoreLog.PhysicalFileName = $server.Information.MasterDBLogPath + "\" + $smoRestore.Database + "_Log.ldf"
$smoRestore.RelocateFiles.Add($smoRestoreFile)
$smoRestore.RelocateFiles.Add($smoRestoreLog)

#restore database
$smoRestore.SqlRestore($server)

您將不得不進入該異常以查看問題所在。Powershell 在名為 $Error 的系統變數中為您跟踪錯誤，該變數是一個堆（即索引 0 是最近的錯誤）。我通常會這樣做：

$e = $error[0]
$e.Exception
$e.Exception.InnerException
$e.Exception.InnerException.InnerException
...

直到我發現真正的錯誤。這可能是實際還原的問題（即具有該名稱的數據庫已經存在，物理文件的路徑錯誤等）。但是直到你找到那個異常的根源，你才會知道！

我遇到了類似的問題，但是我的備份文件中有多個文件，這些文件在目標數據庫上的路徑錯誤，這是完整的解決方案：

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

$LocalFilePath = "C:\code\HQ_Support"       
$backupFile=gi $LocalFilePath\HQ*.bak

#SQL server object
[Microsoft.SqlServer.Management.Smo.Server]$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"

$backupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File")
$smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore

$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"
$smoRestore.PercentCompleteNotification = 10;
$smoRestore.FileNumber = 0
$smoRestore.Devices.Add($backupDevice)

# Get the details from the backup device for the database name and output that
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)
"Database Name from Backup Header : " + $databaseName

$dbLogicalName = ""
$logLogicalName = ""

$logicalFileNameList = $smoRestore.ReadFileList($server)
foreach($row in $logicalFileNameList)
{ 
  $smoRestore.Database = $smoRestoreDetails.Rows[0]["DatabaseName"]

  $fileType = $row["Type"].ToUpper()
  if ($fileType.Equals("D")) 
  {
     $dbLogicalName = $row["LogicalName"]
     $smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile") 
     $smoRestoreFile.LogicalFileName = $dbLogicalName
     $smoRestoreFile.PhysicalFileName = $server.Information.MasterDBPath + "\" + $dbLogicalName + "_Data.mdf"
     $smoRestoreFile
     $smoRestore.RelocateFiles.Add($smoRestoreFile)
  }
  elseif ($fileType.Equals("L")) 
  {
     $logLogicalName = $row["LogicalName"]
     $smoRestoreLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
     $smoRestoreLog.LogicalFileName = $logLogicalName
     $smoRestoreLog.PhysicalFileName = $server.Information.MasterDBPath + "\" + $dbLogicalName + "_Log.ldf"
     $smoRestoreLog
     $smoRestore.RelocateFiles.Add($smoRestoreLog)
  }
}

$server.KillAllProcesses($databaseName)
$smoRestore.SqlRestore($server)

引用自：https://dba.stackexchange.com/questions/31467