SMO restore of SQL database doesn't overwrite

Posted by Tom H. on Stack Overflow See other posts from Stack Overflow or by Tom H.
Published on 2011-01-07T15:49:27Z Indexed on 2011/01/07 15:53 UTC
Read the original article Hit count: 600

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

© Stack Overflow or respective owner

Related posts about sql-server-2008

Related posts about powershell