Feb 082011

T-SQL TuesdayThis Month’s T-SQL Tuesday is being hosted by Pat Wright (blog | twitter) and Pat is asking about tips and tricks for making life easier through automation using either T-SQL or PowerShell.

Recently I have been doing more and more work in PowerShell in order to get familiar with it. So far I’ve been quite happy. Batch scripting always seemed rather clumsy to me, and PowerShell represents a much-needed improvement, especially coming from a .Net programming background like I do.

One of the processes I’ve found particularly helpful to automate in PowerShell is the testing of database backups. A backup is useless if you can’t restore from it, so it’s an excellent idea to make sure you can successfully restore from your backups on a regular basis. Larger databases can take quite a while to restore, and doing it manually can get to be a real pain. Automating the process means it can happen at any time without human intervention, and all I need to do is check my email to determine if the restore succeeded or failed.

How It Works

While the code behind all this (which I’ll get to in a minute) may appear complex, what’s actually happening is pretty simple. The process is built with a few assumptions:
– You are testing a full backup
– Backups are located on a network share that’s easily accessible

The first step is to locate the appropriate backup on the network share and copy it to a temporary folder on the local machine. From there, the database is restored to the local machine with the name specified in the config file. The locations of where to restore the data & transaction log files can also be configured. Finally a DBCC CHECKDB is optionally run once the database is restored, and an email is sent notifying the operator(s) of the outcome and including a log of how the job ran.

The Files

This automated process has two parts: the PowerShell script and an XML config file. I’m a big fan of config files, as they allow me to use the same script on many different systems with different settings. I find this to be much better than the alternative of having lots of slightly different versions of the same script floating around.

The configuration file contains one or more “jobs”, each of which is geared toward restoring a specific database. With all of the configuration settings for a specific database packaged in a job, the number of arguments needed to execute the script is dramatically reduced. There is also a list of email addresses to be alerted upon the success/failure of the script. The configuration file I have included for download is heavily commented to explain the meaning of each setting. I always take the time to write XSDs for my configuration files in hopes of minimizing confusion about their structure – this is included as well.

The PowerShell script itself takes 2 arguments: the name of the job, and the location of the configuration file containing that job. From there, the script reads the configuration file and carries out all the appropriate actions. It requires two modifications before running on your system – the location of the SMTP server that will be sending out notification emails, and an email address it will list as being the sender of said emails.

As with all the scripts you’ll find on my site, these files are provided as-is and with no warranty. It is highly recommended that you read and understand what they are doing before using them at your own risk. Never deploy them to a production system without extensive testing in a development environment.

You can download all the files here. I’ve also posted the PowerShell script below for your viewing pleasure.

Good luck and happy restores!

$ErrorActionPreference = 'Stop';
$global:LOG = "";

# this just helps for printing errors to console
Function PrintUpdate($str) {
	$s = (Get-Date -format HH:mm:ss) + " $str";
	if($host.Name -eq "ConsoleHost") {
		Write-Host $s;
	} else { # no host is present in SQL Agent
		Write-Output $s;
	$global:LOG = $global:LOG + "$s `r`n";

$USAGE = "USAGE: AutoRestore.ps1 <JobName> <ConfigFileName>";

# #1 - Job Name
# #2 - XML File

try {

	#check for required args
	if($args.Length -ne 2) {
		throw $USAGE;

	# get job name
	$jobName = $args[0];

	# load config file for the job
	$configXML = [xml](gc $args[1]);
	$jobXpath = "Job[@name='" + $jobName + "']";
	$jobConfig = $configXML.DocumentElement.SelectSingleNode($jobXpath);
	$emailList = $configXML.DocumentElement.SelectSingleNode("EmailAlertList").InnerText;

	if($jobConfig -eq $null) {
		throw "Cannot locate job '$jobName'";

	PrintUpdate("Starting Job '$jobName'");

	# load variables
	$folderContainingBackups = $jobConfig.SelectSingleNode("BackupStorageFolder").InnerText;
	$backupFileNamePrefix = $jobConfig.SelectSingleNode("BackupFile/@Prefix").Value;
	$localTempFolder = $jobConfig.SelectSingleNode("LocalTempFolder").InnerText;
	$dbToRestoreTo = $jobConfig.SelectSingleNode("DBToRestoreTo").InnerText;
	$folderToRestoreDataTo = $jobConfig.SelectSingleNode("FolderToRestoreDataTo").InnerText;
	$folderToRestoreLogTo = $jobConfig.SelectSingleNode("FolderToRestoreLogTo").InnerText;
	$runConsistencyCheck = $jobConfig.SelectSingleNode("RunConsistencyCheck").InnerText;

	# validate variables
	if(!(Test-Path $folderContainingBackups)) {
		throw "Folder Containing Backups ""$folderContainingBackups"" does not exist!";

	if(!(Test-Path $localTempFolder)) {
		throw "Local Temp Folder ""$localTempFolder"" does not exist!";

	if(!(Test-Path $folderToRestoreDataTo)) {
		throw "Folder To Restore Data To ""$folderToRestoreDataTo"" does not exist!";

	if(!(Test-Path $folderToRestoreLogTo)) {
		throw "Folder To Restore Log To ""$folderToRestoreLogTo"" does not exist!";

	if(!($runConsistencyCheck -match "^0$|^1$")) {
		throw "Invalid RunConsistencyCheck Value";

	# find today's backup file (assumes it was done earlier today)
	$backupFileFullName = dir $folderContainingBackups -name -filter ($backupFileNamePrefix + "_Full_" + (Get-Date -format yyyyMMdd) + "*.bak");

	if($backupFileFullName -eq $null) {
		throw "Backup file not found!";
	PrintUpdate("Found $backupFileFullName");

	# copy backup file to local folder
	PrintUpdate("Copying $backupFileFullName to $localTempFolder");
	copy-item ($folderContainingBackups + "\" + $backupFileFullName) $localTempFolder;

	# drop destination db if it exists
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null;
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null;

	$server = New-Object Microsoft.SqlServer.Management.Smo.Server("(local)");
	$server.ConnectionContext.StatementTimeout = 12000;
	$db = $server.Databases[$dbToRestoreTo];

	if($db -ne $null) {
		PrintUpdate("Dropping DB '$dbToRestoreTo'");
		$server.KillDatabase($dbToRestoreTo); # kills all connections before dropping

	# restore db from backup
	PrintUpdate("Restoring backup To '$dbToRestoreTo'");
	$restoreFileName = ($localTempFolder + "\" + $backupFileFullName);
	$restoreDevice = new-object Microsoft.SqlServer.Management.smo.BackupDeviceItem $restoreFileName, File;
	$smoRestore = new-object Microsoft.SqlServer.Management.Smo.Restore;

	# need to set relocatefiles since machine we're restoring to may not have same drive configuration as production
	$dt = $smoRestore.ReadFileList($server);

	if($folderToRestoreDataTo -eq $null) {
		$dataFileLoc = $server.DefaultFile;
		if($dataFileLoc -eq $null -or $dataFileLoc -eq "") {
			$dataFileLoc = $server.MasterDBPath;
	} else {
		$dataFileLoc = $folderToRestoreDataTo;

	if($folderToRestoreLogTo -eq $null) {
		$logFileLoc = $server.DefaultLog;
		if($logFileLoc -eq $null -or $logFileLoc -eq "") {
			$logFileLoc = $server.MasterDBLogPath;
	} else {
		$logFileLoc = $folderToRestoreLogTo;

	foreach($r in $dt.Rows) {
		$relocateFile = new-object Microsoft.sqlserver.management.smo.relocatefile;
		$relocateFile.LogicalFileName = $r["LogicalName"];
		$oldFilePath = $r["PhysicalName"];
		$n = $oldFilePath.lastindexof("\") + 1;
		if($r["Type"] -eq "D") { #data files
			$relocateFile.PhysicalFileName = $dataFileLoc + "\" + $dbToRestoreTo + "_" + $oldFilePath.substring($n);
		} else { # log file
			$relocateFile.PhysicalFileName = $logFileLoc + "\" + $dbToRestoreTo + "_" + $oldFilePath.substring($n);

	# finally execute the restore function
	$smoRestore.Database = $dbToRestoreTo;

	if($runConsistencyCheck -eq "1") {
		PrintUpdate("Running consistency check on '$dbToRestoreTo'");
		$db = $server.Databases[$dbToRestoreTo];

	PrintUpdate("Job Complete");
	$emailMsg = "completed successfully";

} catch {
	$emailMsg = "encountered errors";
	PrintUpdate("`r`n***ERROR***  " + $_.Exception.ToString());
} finally {
	if($emailList -ne "" -AND $emailList -ne $null) {
		$emailFrom = "sender_email_address";
		$subject = "Job '$jobName' $emailMsg";
		$body = $global:LOG;
		$smtpServer = "smtp_server_name";
		$smtp = new-object Net.Mail.SmtpClient($smtpServer);
		$smtp.Send($emailFrom, $emailList, $subject, $body);

  7 Responses to “T-SQL Tuesday #15: Automation”

  1. Great post and a great way to start my TSQL Tuesday. I’ve just been getting into powershell and this is exactly what I was looking for to start with. I really look foward to digging into your script!


  2. Lovely, thanks! Does it not work on a partial backup?

    • Hi Claire –

      Unfortunately no, this only works on full backups. I have toyed with the idea of adapting it to work with Differential backups as well – if I have time to get around to that I’ll be sure to post it!

  3. […] Bob Pusateri gives us a most excellent script in powershell to test our backups.  I was just telling my team to start a project on this exact topic.  They will be starting at this excellent post.  Thanks Bob! […]

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>