T-SQL Tuesday #15: Automation

T-SQL Tuesday This 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!

  1$ErrorActionPreference = 'Stop';
  2$global:LOG = "";
  3
  4# this just helps for printing errors to console
  5Function PrintUpdate($str) {
  6	$s = (Get-Date -format HH:mm:ss) + " $str";
  7	if($host.Name -eq "ConsoleHost") {
  8		Write-Host $s;
  9	} else { # no host is present in SQL Agent
 10		Write-Output $s;
 11	}
 12	$global:LOG = $global:LOG + "$s `r`n";
 13}
 14
 15$USAGE = "USAGE: AutoRestore.ps1 <JobName> <ConfigFileName>";
 16
 17#Parameters
 18# #1 - Job Name
 19# #2 - XML File
 20
 21try {
 22
 23	#check for required args
 24	if($args.Length -ne 2) {
 25		throw $USAGE;
 26	}
 27
 28	# get job name
 29	$jobName = $args[0];
 30
 31	# load config file for the job
 32	$configXML = [xml](gc $args[1]);
 33	$jobXpath = "Job[@name='" + $jobName + "']";
 34	$jobConfig = $configXML.DocumentElement.SelectSingleNode($jobXpath);
 35	$emailList = $configXML.DocumentElement.SelectSingleNode("EmailAlertList").InnerText;
 36
 37	if($jobConfig -eq $null) {
 38		throw "Cannot locate job '$jobName'";
 39	}
 40
 41	PrintUpdate("Starting Job '$jobName'");
 42
 43	# load variables
 44	$folderContainingBackups = $jobConfig.SelectSingleNode("BackupStorageFolder").InnerText;
 45	$backupFileNamePrefix = $jobConfig.SelectSingleNode("BackupFile/@Prefix").Value;
 46	$localTempFolder = $jobConfig.SelectSingleNode("LocalTempFolder").InnerText;
 47	$dbToRestoreTo = $jobConfig.SelectSingleNode("DBToRestoreTo").InnerText;
 48	$folderToRestoreDataTo = $jobConfig.SelectSingleNode("FolderToRestoreDataTo").InnerText;
 49	$folderToRestoreLogTo = $jobConfig.SelectSingleNode("FolderToRestoreLogTo").InnerText;
 50	$runConsistencyCheck = $jobConfig.SelectSingleNode("RunConsistencyCheck").InnerText;
 51
 52	# validate variables
 53	if(!(Test-Path $folderContainingBackups)) {
 54		throw "Folder Containing Backups ""$folderContainingBackups"" does not exist!";
 55	}
 56
 57	if(!(Test-Path $localTempFolder)) {
 58		throw "Local Temp Folder ""$localTempFolder"" does not exist!";
 59	}
 60
 61	if(!(Test-Path $folderToRestoreDataTo)) {
 62		throw "Folder To Restore Data To ""$folderToRestoreDataTo"" does not exist!";
 63	}
 64
 65	if(!(Test-Path $folderToRestoreLogTo)) {
 66		throw "Folder To Restore Log To ""$folderToRestoreLogTo"" does not exist!";
 67	}
 68
 69	if(!($runConsistencyCheck -match "^0$|^1$")) {
 70		throw "Invalid RunConsistencyCheck Value";
 71	}
 72
 73	# find today's backup file (assumes it was done earlier today)
 74	$backupFileFullName = dir $folderContainingBackups -name -filter ($backupFileNamePrefix + "_Full_" + (Get-Date -format yyyyMMdd) + "*.bak");
 75
 76	if($backupFileFullName -eq $null) {
 77		throw "Backup file not found!";
 78	}
 79	PrintUpdate("Found $backupFileFullName");
 80
 81	# copy backup file to local folder
 82	PrintUpdate("Copying $backupFileFullName to $localTempFolder");
 83	copy-item ($folderContainingBackups + "\" + $backupFileFullName) $localTempFolder;
 84
 85	# drop destination db if it exists
 86	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null;
 87	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null;
 88
 89	$server = New-Object Microsoft.SqlServer.Management.Smo.Server("(local)");
 90	$server.ConnectionContext.StatementTimeout = 12000;
 91	$db = $server.Databases[$dbToRestoreTo];
 92
 93	if($db -ne $null) {
 94		PrintUpdate("Dropping DB '$dbToRestoreTo'");
 95		$server.KillDatabase($dbToRestoreTo); # kills all connections before dropping
 96	}
 97
 98	# restore db from backup
 99	PrintUpdate("Restoring backup To '$dbToRestoreTo'");
100	$restoreFileName = ($localTempFolder + "\" + $backupFileFullName);
101	$restoreDevice = new-object Microsoft.SqlServer.Management.smo.BackupDeviceItem $restoreFileName, File;
102	$smoRestore = new-object Microsoft.SqlServer.Management.Smo.Restore;
103	$smoRestore.Devices.Add($restoreDevice);
104
105	# need to set relocatefiles since machine we're restoring to may not have same drive configuration as production
106	$dt = $smoRestore.ReadFileList($server);
107
108	if($folderToRestoreDataTo -eq $null) {
109		$dataFileLoc = $server.DefaultFile;
110		if($dataFileLoc -eq $null -or $dataFileLoc -eq "") {
111			$dataFileLoc = $server.MasterDBPath;
112		}
113	} else {
114		$dataFileLoc = $folderToRestoreDataTo;
115	}
116
117	if($folderToRestoreLogTo -eq $null) {
118		$logFileLoc = $server.DefaultLog;
119		if($logFileLoc -eq $null -or $logFileLoc -eq "") {
120			$logFileLoc = $server.MasterDBLogPath;
121		}
122	} else {
123		$logFileLoc = $folderToRestoreLogTo;
124	}
125
126	foreach($r in $dt.Rows) {
127		$relocateFile = new-object Microsoft.sqlserver.management.smo.relocatefile;
128		$relocateFile.LogicalFileName = $r["LogicalName"];
129		$oldFilePath = $r["PhysicalName"];
130		$n = $oldFilePath.lastindexof("\") + 1;
131		if($r["Type"] -eq "D") { #data files
132			$relocateFile.PhysicalFileName = $dataFileLoc + "\" + $dbToRestoreTo + "_" + $oldFilePath.substring($n);
133		} else { # log file
134			$relocateFile.PhysicalFileName = $logFileLoc + "\" + $dbToRestoreTo + "_" + $oldFilePath.substring($n);
135		}
136		[void]$smoRestore.RelocateFiles.Add($relocateFile);
137	}
138
139	# finally execute the restore function
140	$smoRestore.Database = $dbToRestoreTo;
141	$smoRestore.SqlRestore($server);
142
143	# run DBCC CHECKDB
144	if($runConsistencyCheck -eq "1") {
145		PrintUpdate("Running consistency check on '$dbToRestoreTo'");
146		$db = $server.Databases[$dbToRestoreTo];
147		$db.CheckTables("None");
148	}
149
150	PrintUpdate("Job Complete");
151	$emailMsg = "completed successfully";
152
153} catch {
154	$emailMsg = "encountered errors";
155	PrintUpdate("`r`n***ERROR***  " + $_.Exception.ToString());
156} finally {
157	if($emailList -ne "" -AND $emailList -ne $null) {
158		$emailFrom = "sender_email_address";
159		$subject = "Job '$jobName' $emailMsg";
160		$body = $global:LOG;
161		$smtpServer = "smtp_server_name";
162		$smtp = new-object Net.Mail.SmtpClient($smtpServer);
163		$smtp.Send($emailFrom, $emailList, $subject, $body);
164	}
165}