Error restoring database backup to new database with smo and powershell

Question

Taking a database backup from another server I'm trying to restore to sqlexpress on the localhost. This restore will work via the gui but I'm having issues restoring it with powershell. I get the following error message:

Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server
+ $smoRestore.SqlRestore <<<< ($server)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

The error message points to character 23 of this line:

        $smoRestore.SqlRestore($server)

Script:

[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

Import-Module PSCX
Import-Module WebAdministration

function GetLatestItem(){
    param([string]$RemotePath)
    $returnString = Get-ChildItem $RemotePath -force -filter "*.7z" | sort @{expression={$_.LastWriteTime}; Descending=$true} | select Name -first 1
    return $returnString.Name
}

function DatabaseExists(){
    param([Microsoft.SqlServer.Management.Smo.Server]$server,[string]$databaseName)
    foreach($database in $server.Databases){
        if($database.Name -eq $databaseName){
            $true
        }
    }
    $false
}

$LocalFile = "C:\backups\backupname.bak.7z"
$LocalFilePath = "C:\backups\"   

Expand-Archive $Localfile $LocalFilePath    

# Most of the restore information was found at http://www.sqlmusings.com/2009/06/01/how-to-restore-sql-server-databases-using-smo-and-powershell/
$backupFile = $LocalFilePath + [IO.Path]::GetFileNameWithoutExtension($LocalFile)
[Microsoft.SqlServer.Management.Smo.Server]$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") ".\SQLEXPRESS"
$backupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File")
$smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore

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

# Get the details from the backup device for the database name and output that
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)
$databaseName = $smoRestoreDetails.Rows[0]["DatabaseName"]

"Database Name from Backup Header : " + $databaseName
$smoRestore.Database = $databaseName    

if(DatabaseExists $server $databaseName -not){
    $smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
    $smoRestoreLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
    $smoRestoreFile.LogicalFileName = $smoRestoreDetails.Rows[0]["DatabaseName"]
    $smoRestoreFile.PhysicalFileName = $server.Information.MasterDBPath + "\" + $smoRestore.Database + "_Data.mdf"
    $smoRestoreLog.LogicalFileName = $smoRestoreDetails.Rows[0]["DatabaseName"] + "_Log"
    $smoRestoreLog.PhysicalFileName = $server.Information.MasterDBPath + "\" + $smoRestore.Database + "_Log.ldf"
    $smoRestore.RelocateFiles.Add($smoRestoreFile)
    $smoRestore.RelocateFiles.Add($smoRestoreLog)
} 

$smoRestore.SqlRestore($server)
if($error.Count -eq 0){
}
else{
    $Error[0].exception.message
}

Blazon

Your question code helped me solve a problem! Thanks :) – Tien

Answer 1

I have a very similar script to yours, with a few noteworthy differences:

• Before calling SqlRestore, I make a call to $server.KillAllProcesses($databaseName).
• I have $smoRestore.NoRecovery = $false, instead of $true
• I have $smoRestore.FileNumber = 1, which you don't have at all. I think this corresponds to checking a file from the backup set in the GUI.

I also have similar code for setting the logical/physical filenames, but instead of using $server.Information, I pull the information from the registry (not sure which is "better"). One other difference is that I use $smoRestore.ReadFileList instead of $smoRestore.ReadBackupHeader.

You might also try using a few Write-Host statements on your paths to make sure they look right, if you haven't already.

Hope one of the bulleted tweaks solves your issue. Let me know if you want more info from my script.

Magnifico

For any future readers, adding "$smoRestore.FileNumber = 1" was the trick that did it for me. – Coahuila

You can also try digging deeper into the exception. Try using: $Error[0].exception.GetBaseException().Message – Reflective

Answer 2

A coworker and I both had this problem, and after a bit of troubleshooting we found that closing SQL Server Management Studio did the trick.

Hopefully someone else can skip all the troubleshooting we did and this easy solution will save them a few hours.

Vann