SMO restore of SQL database doesn't overwrite

Question

I'm trying to restore a database from a backup file using SMO. If the database does not already exist then it works fine. However, if the database already exists then I get no errors, but the database is not overwritten.

The "restore" process still takes just as long, so it looks like it's working and doing a restore, but in the end the database has not changed.

I'm doing this in Powershell using SMO. The code is a bit long, but I've included it below. You'll notice that I do set $restore.ReplaceDatabase = $true. Also, I use a try-catch block and report on any errors (I hope), but none are returned.

Any obvious mistakes? Is it possible that I'm not reporting some error and it's being hidden from me?

Thanks for any help or advice that you can give!

function Invoke-SqlRestore {
    param(
        [string]$backup_file_name,
        [string]$server_name,
        [string]$database_name,
        [switch]$norecovery=$false
    )

    # Get a new connection to the server
    [Microsoft.SqlServer.Management.Smo.Server]$server = New-SMOconnection -server_name $server_name
    Write-Host "Starting restore to $database_name on $server_name."

    Try {
        $backup_device = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backup_file_name, "File")

        # Get local paths to the Database and Log file locations
        If ($server.Settings.DefaultFile.Length -eq 0) {$database_path = $server.Information.MasterDBPath }
        Else { $database_path = $server.Settings.DefaultFile}
        If ($server.Settings.DefaultLog.Length -eq 0 ) {$database_log_path = $server.Information.MasterDBLogPath }
        Else { $database_log_path = $server.Settings.DefaultLog}

        # Load up the Restore object settings
        $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
        $restore.Action = 'Database'
        $restore.Database = $database_name
        $restore.ReplaceDatabase = $true

        if ($norecovery.IsPresent) { $restore.NoRecovery = $true }
        Else { $restore.Norecovery = $false }

        $restore.Devices.Add($backup_device)

        # Get information from the backup file
        $restore_details = $restore.ReadBackupHeader($server)
        $data_files = $restore.ReadFileList($server)

        # Restore all backup files
        ForEach ($data_row in $data_files) {
            $logical_name = $data_row.LogicalName
            $physical_name = Get-FileName -path $data_row.PhysicalName

            $restore_data = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
            $restore_data.LogicalFileName = $logical_name

            if ($data_row.Type -eq "D") {
                # Restore Data file
                $restore_data.PhysicalFileName = $database_path + "\" + $physical_name
            }
            Else {
                # Restore Log file
                $restore_data.PhysicalFileName = $database_log_path + "\" + $physical_name
            }
            [Void]$restore.RelocateFiles.Add($restore_data)
        }

        $restore.SqlRestore($server)

        # If there are two files, assume the next is a Log
        if ($restore_details.Rows.Count -gt 1) {
            $restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
            $restore.FileNumber = 2
            $restore.SqlRestore($server)
        }
    }
    Catch {
        $ex = $_.Exception
        Write-Output $ex.message
        $ex = $ex.InnerException
        while ($ex.InnerException) {
            Write-Output $ex.InnerException.message
            $ex = $ex.InnerException
        }
        Throw $ex
    }
    Finally {
        $server.ConnectionContext.Disconnect()
    }
    Write-Host "Restore ended without any errors."
}

Answer 1

I having the same problem, I'm trying to restore the database from a back taken from the same server but with a different name. I have profiled the restore process and it doesn't add the 'with move' with the different file names. This is why it will restore the database when the database doesn't exist,but fail when it does. There is an issue with the .PhysicalFileName property.