Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

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

#restore database
share|improve this question

migrated from Jan 5 '13 at 1:40

This question came from our site for professional and enthusiast programmers.

I'm curious as to why PowerShell to do this? It seams that T/SQL would be shorter code and much easier to debug. – mrdenny Jan 5 '13 at 2:15


# Set SQL Server instance name
$sqlName= "localhost\SQLExpress"

# Set new or existing databse name to restote backup
$dbname= "MorganDB"

# Set the existing backup file path
$backupPath= "D:\SQLBackup\MorganDB.bak"

#Load the required assemlies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 

# Connect SQL Server.
$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName

# Create SMo Restore object instance
$dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")

# Set database and backup file path
$dbRestore.Database = $dbname
$dbRestore.Devices.AddDevice($backupPath, "File")

# Set the databse file location
$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreFile.LogicalFileName = $dbname
$dbRestoreFile.PhysicalFileName = $sqlServer.Information.MasterDBPath + "\" + $dbRestore.Database + "_Data.mdf"
$dbRestoreLog.LogicalFileName = $dbname + "_Log"
$dbRestoreLog.PhysicalFileName = $sqlServer.Information.MasterDBLogPath + "\" + $dbRestore.Database + "_Log.ldf"

# Call the SqlRestore mathod to complete restore database 

Write-Host "...SQL Database"$dbname" Restored Successfullyy..."
share|improve this answer

I was having a similar issue but my backup file had multiple files in it that had paths that were wrong on the target DB, here is the complete solution:

[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

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

share|improve this answer
I did approximately the same. But I have to deal with FileStream too ... $fileType.Equals("S"). But when I'm restoring my db, it's rewriting my .hdr. $smoRestoreFile.PhysicalFileName = Join-Path -Path $pathBUjour_Drive -ChildPath ($name_db) This is the line, should I suffix like this : ($name_db + '_New.hdr') in order to keep the original HDR file not modified ? – Nico Aug 25 '15 at 8:55

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]

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!

share|improve this answer
$error[0] | fl -force will help – Chad Miller Jan 4 '13 at 23:20

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.