Sql-server – Restore SQL Server database using Windows Powershell 3.0

backuppowershellsql server

I'm trying to restore a SQL Server database with a PowerShell script, but I'm having problems.

Here is the error I'm getting:

Exception calling "SqlRestore" with "1" argument(s): "Restore failed
for Server 'WUSFK250042-OLU\SQLSERVER2008R2'. " At line:48 char:1
+ $smoRestore.SqlRestore($server)

Here is my code:

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

Best Answer

You're going to have to get into that exception to see what the problem is. Powershell keeps track of errors for you in a system variable called $Error, which is a heap (i.e. index 0 is the most recent error). I usually do something like this:

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

Until I find the real error. It could be a problem with the actual restore (i.e. database with that name already exists, bad paths for the physical files, etc). But until you get to the root of that exception you won't know!

Related Question