Tag Archives: restore

Powershell Restore to New SQL Server Database

I don’t often have to restore a database. However, while having to restore a few dozen to set up database mirroring, I came across a few with multiple database files. My first ‘mirroring’ script did not account for this. I quickly wrote a new restore script to accommodate the possibility of multiple files.

<#
     .SYNOPSIS
          Restore to a NEW Database
     .DESCRIPTION
          Restores an SQL Server backup file to a new database.
	   1) Uses existing Logical and physical file names and restores to the
	      SQL server default file locations.
	   2) Restores multiple files.
	   3) Can specify NoRecover (necessary for mirrored database)
     .PARAMETER  file
          Full path and file name for the backup file. Must be local
     .PARAMETER  Server
          The name\instance of the SQL Server.
     .PARAMETER  database
          The name of the database to be restored
     .EXAMPLE
          PS C:\> Invoke-SqlRestore -file 'D:\Backups\mydb.bak' `
		  	-server 'MyServr\SQLinstance' -database NEWDB
     .NOTES
         AUTHOR:    John P. Wood
         CREATED:   July, 2010
         VERSION:   1.0.3
     .LINK
         http://www.webofwood.com
#>
Param(
	[string]$file,
	[string]$server,
	[string]$database,
	[switch]$norecovery=$true
	)
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
Function New-SMOconnection {
    Param ([string]$server)
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = 0
	$conn.Connect()
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to server $($server)."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
}
Function Get-FileName {
	Param([string]$path)
	$names = $path.Split('\\')
	$names[$names.Count - 1]
}
Function Invoke-SqlRestore {
	Param(
		[string]$backupFile,
		[Microsoft.SqlServer.Management.Smo.Server]$smo
	)
	$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") `
		($backupFile, "File")
	# Get local paths to the Database and Log file locations
	If ($smo.Settings.DefaultFile.Length -eq 0) {$DBPath = $smo.Information.MasterDBPath }
	Else { $DBPath = $smo.Settings.DefaultFile}
	If ($smo.Settings.DefaultLog.Length -eq 0 ) {$DBLogPath = $smo.Information.MasterDBLogPath }
	Else { $DBLogPath = $smo.Settings.DefaultLog}

	# Load up the Restore object settings
	$Restore = new-object Microsoft.SqlServer.Management.Smo.Restore
	$Restore.Action = 'Database'
	$Restore.Database = $database
    $Restore.ReplaceDatabase = $true
	if ($norecovery.IsPresent) { $Restore.NoRecovery = $true }
	Else { $Restore.Norecovery = $false }
	$Restore.Devices.Add($backupDevice)

	# Get information from the backup file
	$RestoreDetails = $Restore.ReadBackupHeader($smo)
	$DataFiles = $Restore.ReadFileList($smo)

	# Restore all backup files
	ForEach ($DataRow in $DataFiles) {
        $LogicalName = $DataRow.LogicalName
        $PhysicalName = Get-FileName -path $DataRow.PhysicalName
		$RestoreData = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
		$RestoreData.LogicalFileName = $LogicalName
		if ($DataRow.Type -eq "D") {
			# Restore Data file
			$RestoreData.PhysicalFileName = $DBPath + "\" + $PhysicalName
		}
		Else {
			# Restore Log file
			$RestoreData.PhysicalFileName = $DBLogPath + "\" + $PhysicalName
		}
		[Void]$Restore.RelocateFiles.Add($RestoreData)

	}
	$Restore.SqlRestore($smo)
	# If there are two files, assume the next is a Log
	if ($RestoreDetails.Rows.Count -gt 1) {
		$Restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
		$Restore.FileNumber = 2
		$Restore.SqlRestore($smo)
	}
}
Clear-Host
# Get a new connection to the server
$smo = New-SMOconnection -server $server
Write-Host "Starting restore to New Database $database on $server."
Try {
	Invoke-SqlRestore -backupFile $file -smo $smo
	}
Catch {
	$ex = $_.Exception
	Write-Output $ex.message
	$ex = $ex.InnerException
	while ($ex.InnerException) {
		Write-Output $ex.InnerException.message
		$ex = $ex.InnerException
	}
	Throw $ex
}
Finally {
	$smo.ConnectionContext.Disconnect()
}
Write-Host "Restore ended without any errors."
	 1) Uses existing Logical and physical file names and restores to the
	    SQL server default file locations.
	 2) Restores multiple files.
	 3) Can specify NoRecover (necessary for mirrored database)