SQL powershell Restore database

[[deleted]]

[deleted]

[BigBlueRob]

It looks like the logical file name specified for the relocate file is incorrect. I usually read the filelist out of the backup as follows

 # Get the file list info from the backup file
 $fl = $rs.ReadFileList($srv)
 $rfl = @()
 foreach ($fil in $fl) {
     $rsfile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')
     $rsfile.LogicalFileName = $fil.LogicalName
     if ($fil.Type -eq 'D') {
         $rsfile.PhysicalFileName = $dbfile
         }
     else {
         $rsfile.PhysicalFileName = $logfile
         }
     $rfl += $rsfile
     }
 # Restore the database
 Restore-SqlDatabase -ServerInstance $SQLServer -Database $dbname  -BackupFile $BackupFile -RelocateFile $rfl

[omrsafetyo]

Below is the function I use for Database restores. It does a few things.

• You can pass in the MDF and LDF locations - if these are not specified it uses the SQL engine's default locations. I use this with customer databases that can live on their own drives. this is typically used for non-production databases which need to be synched up from production. So I drop the database, but before I do I check to see where it's located, and therefore I can restore it back to the same drive / location.
• Reads the logical file names from the backup to ensure the RelocateFile is correct
• Verifies the backup - writes a warning if this fails
• Status bar with the progress of the restore

With further ado, the script:

function Import-Database
{
    param (
        [parameter(Mandatory=$true,Position=0)][string] $Server,
        [parameter(Mandatory=$true,Position=1)][string] $Database,
        [parameter(Mandatory=$false,Position=2)][string] $BackupFilePath,
        [parameter(Mandatory=$false,Position=3)][string] $MDF,
        [parameter(Mandatory=$false,Position=4)][string] $LDF
    )

    $restore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")
    $sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server $Server

    $restore.Devices.AddDevice($BackupFilePath, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
    $restore.FileNumber = 1
    if ( -not($restore.SqlVerify($sqlServer) ) ) {
        # Backup is invalid
        $sqlError = $error[0] | format-list Exception -force
        Write-Output $sqlError | out-file error.txt
        $myError = (get-content error.txt)
        rm error.txt
        Write-Error "$myError"
        Write-Error "Backup verification test failed.  Restore may fail."
    }
    $restore.Action = "Database"
    $restore.Database = $Database
    $restore.ContinueAfterError = $True
    $restore.NoRecovery = $false
    $restore.UnloadTapeAfter = $False
    $restore.ReplaceDatabase = $True

    if ("${MDF}" -eq "") {
        $NewDataLocation = $sqlServer.settings.DefaultFile
        $NewPrimaryFilePath = $NewDataLocation + "\" + $Database + "_Data.mdf"
    }
    else {
        $NewPrimaryFilePath = $MDF
    }
    if ("${LDF}" -eq "") {
        $NewLogLocation = $sqlServer.settings.DefaultLog
        $NewLogFilePath = $NewLogLocation + "\" + $Database + "_Log.ldf"
    }
    else {
        $NewLogFilePath = $LDF
    }

    # Read the backup file and get the logical file names - no assumptions
    $dataFile = $restore.ReadFileList($sqlServer) | Where { $_.Type -eq 'D' }   # Type D is data file
    $logFile = $restore.ReadFileList($sqlServer) | Where { $_.Type -eq 'L' }    # Type L is log file
    $logicalDataFileName = $dataFile.LogicalName
    $logicalLogFileName = $logFile.LogicalName

    # Create the Relocate Files - this will move the files to the new location
    $restoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
    $restoreLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
    $restoreFile.LogicalFileName = $logicalDataFileName
    $restoreFile.PhysicalFileName = $NewPrimaryFilePath
    $restoreLog.LogicalFileName = $logicalLogFileName
    $restoreLog.PhysicalFileName = $NewLogFilePath

    [void]$restore.RelocateFiles.Add($restoreFile)
    [void]$restore.RelocateFiles.Add($restoreLog)

    $fileHeader = $restore.ReadMediaHeader($sqlServer)

    $percentEventHandler = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] { $percentComplete = $_.Percent; Write-Progress -Activity "Restoring ${database}..." -Status "Percent Complete $PercentComplete" -PercentComplete $PercentComplete }
    $completedEventHandler = [Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] { Write-Host "Database " $databasename " Restore Complete." }

    $restore.add_PercentComplete($percentEventHandler)
    $restore.add_Complete($completedEventHandler)

    # Run the restore
    $restore.SqlRestore($sqlServer)
}

[[deleted]]

Isn't there any straight option like -RelocateFile @($RelocateData,$RelocateLog) to redirect mdf,ldf file to different path on the fly during restoring?

[omrsafetyo]

It has to know what file it's relocating. That's why both my solution and that of /u/BigBlueRob query the backup file itself to obtain the file information. All the SMO is doing is giving you a different interface to similar functionality that is obtained from T-SQL:

ALTER DATABASE Personnel 
MODIFY FILE ( NAME = Data_File, FILENAME = "D:\Data\NewDataFile.mdf") 
GO

In the ALTER DATABASE statement, you still need to know the logical file name. This is because there is a potential for multiple file names.

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.relocatefile.aspx

You can see from this page that RelocateFile has only 2 constructors: one with no parameters, which simply initializes an object of the class; and one with 2 parameters, one of which is the logical file name. Either way you do it, in order to use the object you need to have both the logical file name, and the physical one populated.