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
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)
share|improve this question

migrated from stackoverflow.com Jan 5 '13 at 1:40

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

I had this problem today and I created a CmdLet to realize Backup and Restore. I put it on github: SqlBackupRestore.ps1 It is also reproduced below:

#region SQL Assemblies
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
#endregion SQL Assemblies


function New-SqlBackup(){
    param
    (
        [parameter(Mandatory=$true)]
        [String]
        $Instance,

        [parameter(Mandatory=$false)]
        [String]
        $Database
    )

    $svr = new-Object Microsoft.SqlServer.Management.Smo.Server($Instance)
    while(!$Database){
        Write-Host $svr.Databases
        $Database = (Read-Host -Prompt "Type the database's name:") | % {$_.Trim()}
    }
    Write-Host 'Gerando backup...'

    $dt = Get-Date -Format yyyyMMddHHmmss
    $bdir = $svr.Settings.BackupDirectory
    $db = $svr.Databases[$database]
    $dbname = $db.Name

    $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
    $dbbk.Action = 'Database'

    $dbbk.BackupSetDescription = "Full backup of " + $dbname
    $dbbk.BackupSetName = $dbname + " Backup"
    $dbbk.Database = $dbname
    $dbbk.MediaDescription = "Disk"
    $dbbk.Devices.AddDevice($bdir + "\" + $dbname + "_db_" + $dt + ".bak", 'File')
    $dbbk.SqlBackup($svr.Name)
}

function New-SqlRestore(){
    param
    (
        [parameter(Mandatory=$true)]
        [String]
        $Instance,

        [parameter(Mandatory=$false)]
        [String]
        $Database,

        [parameter(Mandatory=$false)]
        [String]
        $FileName
    )

    $svr = new-Object Microsoft.SqlServer.Management.Smo.Server($Instance)

     while(!$Database){
        Write-Host $svr.Databases
        $Database = (Read-Host -Prompt "Type the database's name.") | % {$_.Trim()}
    }

    $bdir = $svr.Settings.BackupDirectory

    $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')

    while(!$FileName){
        Write-Host (Get-ChildItem $bdir)
        $FileName = (Read-Host -Prompt "Type the filename:") | % {$_.Trim()}
    }
    Write-Host 'Restoring...'

    $BackupFile = Get-ChildItem $bdir -Filter $FileName | select -First 1

    Write-Host $BackupFile

    $db = $svr.Databases[$Database]

    $dbname = $db.Name

    $smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore

    $smoRestore.PercentCompleteNotification = 10;    
    $smoRestore.Database = $dbname
    $smoRestore.NoRecovery = $false
    $smoRestore.ReplaceDatabase = $true
    $smoRestore.FileNumber = 0

    $bdi = new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem($BackupFile.FullName, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
    $smoRestore.Devices.Add($bdi)

    $svr.KillAllProcesses($dbname)
    $smoRestore.SqlRestore($svr)
}
share|improve this answer
    
Does this solve a problem for you which sqlps's Restore-SQLDatabase cmdlet doesn't? – alroc Feb 28 at 14:42
    
Hi @alroc, I think yes. My purpose with this script is to show how and where it can be custom. But, I agree you that 'sqlps' module works for this scenario. – Jonatan Machado Mar 5 at 13:54

From http://www.morgantechspace.com/2014/11/Powershell-script-to-Backup-and-Restore-SQL-Database.html

# 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"
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)

# Call the SqlRestore mathod to complete restore database 
$dbRestore.SqlRestore($sqlServer)

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

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

Your Answer

 
discard

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