Behind Every Great Restore is a Great Backup

I showed you how to do a database backup with powershell a few days ago. Today I'll show you how to do the restore. Restores can sometimes be quite a bit more complicated than a backup. You have to account for the possibility that the database file names may already be in use by another database or the database may already exist. If the database does already exist, the restore process requires exclusive access to the database.

A Powershell Restore Script

This script accepts 3 parameters for the server name, the database name, and the path and name of the backup. The previous backup script returns the path and name of the backup upon successful completion. If you are backing up the database in order to restore it someplace else, you can use this return value as a parameter for the restore script. I show an example of this further down.

Parameters:

1. $Server: [String], Name of server, Required.
2. $Database: [String], Name of database, Required.
3. $Backup: [String], Backup path and name, Required.

Example Usage:

.\RestoreDB.ps1 MyServer MyDatabase "c:\mssql\BAK\MyDatabase.bak"

Using the backup and restore scripts together:

$BAK = .\BackupDB.ps1 MyServer MyDatabase "c:\mssql\BAK"

.\RestoreDB.ps1 MyServer MyDatabase $BAK

A real world demo:

PS C:\Windows\System32\WindowsPowerShell\v1.0> pushd C:\Users\v-rodav\Documents\Powershell
PS C:\Users\v-rodav\Documents\Powershell> $BAK = .\BackupDB.ps1 "v-rodav4" PSP "C:\bak"
        Database PSP backed up to C:\bak\PSP\PSP_backup_20090422210339.bak
PS C:\Users\v-rodav\Documents\Powershell> .\RestoreDB.ps1 "v-rodav4" PSP $BAK
        Database PSP restored from C:\bak\PSP\PSP_backup_20090422210339.bak

The Script

param (
        [string] $Server,
        [string] $Database,
        [string] $Backup
)

## Path and name used to invoke script
$CUR_SCRIPT = $myinvocation.InvocationName

## Load SMO assemblies
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")|out-null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")|out-null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null
$SMO = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
## Parse out the internal version number
$SMOVer = $SMO.FullName.Split(",")[1].Split("=")[1].Split(".")[0]
## Load SMOExtended if not SQL Server 2005 (9)
if ($SMOVer -ne 9) {
        [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")|out-null
}

## Check user input, prompt for each value not provided as parameters
if(!$Server) { $Server = read-host "Enter Server Name" }
if(!$Database) { $Database = read-host "Enter Database Name" }
if(!$Backup) { $Backup = read-host "Enter Backup Path\Name" }

## Return Help and exit if any required input is missing
if(!$Server -or !$Database -or !$Backup) {
        write-host "Usage: $CUR_SCRIPT options:
        string SQL Server Instance
        string Database Name
        string Backup Path and Name" -f red
        exit
}

## Function to raise error
Function RaisError ([string]$ErrMsg){
        write-host $ErrMsg -f red
        $error.clear()
}

## Create server object
$Srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Server

## Make sure backup file exists
$BackupExists = Test-Path $Backup
if (!$BackupExists) {
        RaisError "`tBackup file does not exist or is not accessible."
        Exit
}

## If database exists, delete it
$DBExists = $Srv.Databases[$Database]
if ($DBExists) {
        if ($DBExists.status -eq "online") {
                $Srv.KillDatabase($Database)
        } else {
                $DBExists.drop()
        }
        if ($error){
                RaisError "`tDrop of existing database returned an error."
                Exit
        }
}

## Restore the database
$Restore = new-object "Microsoft.SqlServer.Management.Smo.Restore"
$Restore.Database = $Database
$Restore.Action = 'Database'
$BkFile = new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$BkFile.DeviceType = 'File'
$BkFile.Name = $Backup
$Restore.Devices.Add($BkFile)
$Restore.ReplaceDatabase = $false

## Check file list and generate new file names if files already exists
$DateSerial = Get-Date -Format yyyyMMddHHmmss
$DataFiles = $Restore.ReadFileList($Server)
ForEach ($DataRow in $DataFiles) {
        $LogicalName = $DataRow.LogicalName
        $PhysicalName = $DataRow.PhysicalName
        $FileExists = Test-Path $PhysicalName
        if ($FileExists) {
                $PhysicalName = $PhysicalName -replace(".mdf", "_$DateSerial.mdf")
                $PhysicalName = $PhysicalName -replace(".ldf", "_$DateSerial.ldf")
                $PhysicalName = $PhysicalName -replace(".ndf", "_$DateSerial.ndf")
                $Restore.RelocateFiles.Add((new-object microsoft.sqlserver.management.smo.relocatefile -ArgumentList $LogicalName, $PhysicalName)) | out-null;
        }
}
$Restore.NoRecovery = $false
$Restore.PercentCompleteNotification = 5
$Restore.SqlRestore($Server)
if (!$error){
        write-host "`tDatabase $Database restored from $Backup" -f green
} else {
        RaisError "`tRestore of database $Database returned an error."
        Exit
}