4

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

5 Answers 5

3

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!

1
  • 1
    $error[0] | fl -force will help Jan 4, 2013 at 23:20
3

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)
1
  • 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, 2015 at 8:55
1

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)
}
2
  • Does this solve a problem for you which sqlps's Restore-SQLDatabase cmdlet doesn't?
    – alroc
    Feb 28, 2016 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. Mar 5, 2016 at 13:54
1

Instead of using SMO .NET libraries, you can restore SQL Server using the SqlServer PowerShell module. You can install the SqlServer module using the following command:

Install-Module -Name SqlServer

Then you can restore the database with the command Restore-SqlDatabase

Restore-SqlDatabase -ServerInstance '.' -Database [database-name] -BackupFile 'C:\Backup\backup_file.bak' -ReplaceDatabase

Also, you can execute any T-SQL command using the sqlcmd utility, which can also be run from PowerShell.

About the SqlServer module

About the Restore-SqlDatabase command

SQL Server restore examples

-2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.