Restore Latest Backup with Powershell

#########################################################################################
# Title:	Restore-LatestBackup.ps1
# Created:	3/29/2013, Luke Losli
# Usage:	Restores the latest backup of a database to a destination server of your choice.
#			If no full backup exists, or if the $newBackup switch is on, a new backup is created.
#
#########################################################################################
param
(
	[string]$dbName = "",
	[string]$sourceServerInstance = "",
	[string]$destServerInstance = "",
	[string]$newDbName = "",
	[switch]$newBackup = $false,
	[switch]$sqlAuth = $false,
	[string]$login = "",
	[string]$password = ""
)

#Settings You Might Want to Adjust#####################
$backupFolder = "\\192.168.1.100\SQLBACKUP";
$sqlTimeoutPeriod = 1800;	#Default is 10 min (600 sec), which is too short for some restores
#######################################################

#Debug/Test Values##########################################
#$dbName = "GoDucks";
#$sourceServerInstance = "DUCKSQL01\DUCKSQL";
#$destServerInstance = "DUCKSQL02\DUCKSQL";
#$newDbName = $dbName + "_2";
#$newBackup = $true;
#$sqlAuth = $true;
#$login = "sqlLogin";
#$password = "strongPw";
#######################################################

Clear-Host

#Load required assemblies
	[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo");
	[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
	[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended");
	
#Write Errors to Log or Console
Trap [Exception] 
{
	$err = $_.Exception
	while ( $err.InnerException )
    {
    	$err = $err.InnerException;
		$errorfullmessage += $err.Message + " ";
	};
	#$errorfullmessage | out-File ("C:\temp\ERROR_RestoreLatestBackup.txt") -Append;
	Write-Host $errorfullmessage;
	break;
}

#Check parameters
if (($dbName -eq "") -or ($sourceServerInstance -eq "") -or ($destServerInstance -eq ""))
{
	Write-Host "dbName, sourceServerInstance and destServerInstance parameters are required!" -ForegroundColor Red;
	Write-Host "Usage: .\RestoreLatestBackup.ps1 -dbName -sourceServerInstance -destServerInstance" -ForegroundColor Red;
	Write-Host "Optional: -newDBName -newBackup -sqlAuth -login -password" -ForegroundColor Red;
	break;
}
if (($sqlAuth -eq $true) -and (($login -eq "") -or ($password -eq "")))
{
	Write-Host "If sqlAuth parameter is true, login and password must be provided!" -ForegroundColor Red;
	Write-Host "Usage: .\RestoreLatestBackup.ps1 -dbName -sourceServerInstance -destServerInstance" -ForegroundColor Red;
	Write-Host "Optional: -newDBName -newBackup -sqlAuth -login -password" -ForegroundColor Red;
	break;
}
if (($sourceServerInstance -eq $destServerInstance) -and ($newDbName -eq ""))
{
	Write-Host "If the newDbName parameter is not provided, then source and destination servers must be different!" -ForegroundColor Red;
	break;
}
if ($newDbName -eq "")
{
	$newDbName = $dbName;
}

Write-Host "Starting restore process for database $dbName from $sourceServerInstance to $destServerInstance" -ForegroundColor DarkGreen;
Write-Host "Checking for $newDbName on destination server..." -ForegroundColor DarkGreen;

#Make sure db doesn't already exist on destination server
$destServerConnection = New-Object ('Microsoft.SqlServer.Management.Common.ServerConnection');
$destServerConnection.ServerInstance = $destServerInstance;
if ($sqlAuth -eq $true)
{
	$destServerConnection.LoginSecure = $false;
	$destServerConnection.Login = $login;
	$destServerConnection.Password = $password;
}
else
{
	$destServerConnection.LoginSecure = $true;
}

$smoDestination = New-Object ('Microsoft.Sqlserver.Management.Smo.Server') ($destServerConnection);
$smoDestination.ConnectionContext.StatementTimeout = $sqlTimeoutPeriod;

$dbOnline = $null;
$dbOnline = $smoDestination.Databases | Where-Object {$_.Name -eq $newDbName};
if ($dbOnline -ne $null)
{
	Write-Host "Database $newDbName already exists on $destServerInstance! RESTORE FAIL." -ForegroundColor Red;
	break;		
}

Write-Host "Database $newDbName not found on $destServerInstance. Proceeding..." -ForegroundColor DarkGreen;

#Parse server and instance names
$parsedSourceServerInstance = $sourceServerInstance.split("\");
$sourceServer = $parsedSourceServerInstance[0];
$sourceInstance = $parsedSourceServerInstance[1];

$parsedDestServerInstance = $destServerInstance.split("\");
$destServer = $parsedDestServerInstance[0];
$destInstance = $parsedDestServerInstance[1];

#Setup source server connection
$sourceServerConnection = New-Object ('Microsoft.SqlServer.Management.Common.ServerConnection');
$sourceServerConnection.ServerInstance = $sourceServerInstance;
if ($sqlAuth -eq $true)
{
	$sourceServerConnection.LoginSecure = $false;
	$sourceServerConnection.Login = $login;
	$sourceServerConnection.Password = $password;
}
else
{
	$sourceServerConnection.LoginSecure = $true;
}
$smoSource = New-Object ('Microsoft.SqlServer.Management.Smo.Server') ($sourceServerConnection);

#Get most recent backup filename from msdb
$backupFilePath = $null;
	
if ($newBackup -eq $false)
{
	$sourceMsdb = $smoSource.Databases["msdb"];
	$latestBackupQuery = "SELECT	TOP 1 bmf.physical_device_name as Filename
	FROM	msdb.dbo.backupset bs
	JOIN	msdb.dbo.backupmediafamily bmf
		ON (bs.media_set_id = bmf.media_set_id
		AND bs.database_name = '$dbName'
		AND bs.type = 'D')
	ORDER BY bs.backup_finish_date DESC";
	$backupFilePath = $sourceMsdb.ExecuteWithResults($latestBackupQuery).Tables[0].Rows[0].Filename;

	if ($backupFilePath -eq $null)
	{
		Write-Host "No full backup found in msdb.dbo.backupset! Taking a new full backup..." -ForegroundColor DarkGreen;
		$newBackup = $true;
	}
	else
	{
		#See if the backup file actually exists where expected
		$backupExists = Test-Path $backupFilePath;
		if ($backupExists -eq $false)
		{
			Write-Host "Could not locate backup file $backupFilePath! Taking a new full backup..." -ForegroundColor DarkGreen;
			$newBackup = $true;
		}
		else
		{
			Write-Host "Most recent full backup has been located." -ForegroundColor DarkGreen;
		}
	}
}

#Take a new full backup if needed
if ($newBackup -eq $true)
{
	$timestamp = Get-Date -Format yyyyMMddHHmmss;
	$backupFilePath = $backupFolder + "\" + $dbName + "_FULL_" + $timestamp + ".bak";
	$smoNewBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup;
	$smoNewBackup.Action = "Database";
	$smoNewBackup.CopyOnly = $true;
	$smoNewBackup.Database = $dbName;
	$smoNewBackup.Devices.AddDevice($backupFilePath, "File");
	$smoNewBackup.Initialize = $true;
	$smoNewBackup.SqlBackup($sourceServerConnection);
	Write-Host "Full backup $backupFilePath created successfully!" -ForegroundColor DarkGreen;	
}
	
Write-host "Restoring file $backupFilePath as $newDbName on $destServerInstance" -ForegroundColor DarkGreen;

#Prepare to restore the database on the destination server
$smoNewDB = New-Object Microsoft.Sqlserver.Management.Smo.Restore;
$smoNewDB.Devices.AddDevice($backupFilePath, "File");
$smoNewDB.NoRecovery = $false;
$smoNewDB.Action = "Database";
$smoNewDB.Database =  $newDbName;
$smoNewDB.ReplaceDatabase = $false;
$backupFiles = $smoNewDB.ReadFileList($destServerConnection); 

# Loop through backup file and configure data and log file names. 
$logicalFilename = $null;
$physicalFilename = $null;

foreach ($dbFile IN $backupFiles.Rows)	{
	$logicalFilename = $dbFile.LogicalName;
	$physicalFilename = $dbFile.PhysicalName.Replace($sourceInstance, $destInstance).Replace($dbName, $newDbName);
	
	$smoMoveFile = New-Object('Microsoft.SqlServer.Management.Smo.RelocateFile');
	$smoMoveFile.LogicalFileName = $logicalFilename;
	$smoMoveFile.PhysicalFileName = $physicalFilename;
	$smoNewDB.RelocateFiles.Add($smoMoveFile);
}

#Restore db
$smoNewDB.SqlRestore($destServerConnection);

Write-Host "SUCCESS! Database Restored as $newDbName!" -ForegroundColor DarkGreen;