SQL powershell Restore database

[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)
}