SQL SMO database recovery not overwritten

Question

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

The “restore” process still takes the same amount of time, so it looks like it is working and performing a restore, but in the end the database has not changed.

I do this in Powershell using SMO. The code is a bit long, but I have included it below. You will notice that I installed $restore.ReplaceDatabase = $true. In addition, I use the try-catch block and report any errors (hopefully), but none of them return.

Any obvious errors? Is it possible that I am not reporting an error and hiding it from me?

Thanks for any help or advice 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 have the same problem, I'm trying to restore the database from the reverse side, taken from the same server, but with a different name. I have profiled the recovery process and it doesn’t add “move” with different file names. That is why it will restore the database when the database does not exist, but its execution fails. There is a problem with the .PhysicalFileName property.

Answer 2

I did SMO recovery and encountered errors. The only way I found to diagnose the problem is to run the SQL profile while running my powershell script.

T-SQL, . . : , , .

script , .

Param
(
[Parameter(Mandatory=$True)][string]$sqlServerName,
[Parameter(Mandatory=$True)][string]$backupFile,
[Parameter(Mandatory=$True)][string]$newDBName
)

       # Load assemblies
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
        [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
        # Create sql server object

        $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $sqlServerName
        # Copy database locally if backup file is on a network share

        Write-Host "Loaded assemblies"

        $backupDirectory = $server.Settings.BackupDirectory
        Write-Host "Backup Directory:" $backupDirectory

        $fullBackupFile = $backupDirectory + "\" + $backupFile

        Write-Host "Copy DB from: " $fullBackupFile


       # Create restore object and specify its settings
        $smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
        $smoRestore.Database = $newDBName
        $smoRestore.NoRecovery = $false;
        $smoRestore.ReplaceDatabase = $true;
        $smoRestore.Action = "Database" 

        Write-Host "New Database name:" $newDBName

        # Create location to restore from
        $backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($fullBackupFile, "File")
        $smoRestore.Devices.Add($backupDevice)

        # Give empty string a nice name
        $empty = ""

        # Specify new data file (mdf)
        $smoRestoreDataFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
        $defaultData = $server.DefaultFile
        if (($defaultData -eq $null) -or ($defaultData -eq $empty))
        {
            $defaultData = $server.MasterDBPath
        }

        Write-Host "defaultData:" $defaultData

        $smoRestoreDataFile.PhysicalFileName = Join-Path -Path $defaultData -ChildPath ($newDBName + "_Data.mdf")

        Write-Host "smoRestoreDataFile.PhysicalFileName:" $smoRestoreDataFile.PhysicalFileName

        # Specify new log file (ldf)
        $smoRestoreLogFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
        $defaultLog = $server.DefaultLog
        if (($defaultLog -eq $null) -or ($defaultLog -eq $empty))
        {
            $defaultLog = $server.MasterDBLogPath
        }
        $smoRestoreLogFile.PhysicalFileName = Join-Path -Path $defaultLog -ChildPath ($newDBName + "_Log.ldf")

        Write-Host "smoRestoreLogFile:" $smoRestoreLogFile.PhysicalFileName

        # Get the file list from backup file
        $dbFileList = $smoRestore.ReadFileList($server)

        # The logical file names should be the logical filename stored in the backup media
        $smoRestoreDataFile.LogicalFileName = $dbFileList.Select("Type = 'D'")[0].LogicalName
        $smoRestoreLogFile.LogicalFileName = $dbFileList.Select("Type = 'L'")[0].LogicalName
        # Add the new data and log files to relocate to
        $smoRestore.RelocateFiles.Add($smoRestoreDataFile)
        $smoRestore.RelocateFiles.Add($smoRestoreLogFile)

        # Restore the database
        $smoRestore.SqlRestore($server)

        "Database restore completed successfully"

Answer 3

, T-SQL, - , . , , ( ). db. , ; , , , , , . , .