T-SQL Tuesday #15: Automation
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}