Database Restore Testing Automation Presentation given at the

Database Restore Testing Automation Presentation given at the Nov 2016 SLC SQL Saturday Event by Robert Every

About me Career • Graduated BYU April 1983 • General Electric Ordnance Systems • General Electric Motors… 1983 – 1985 – 2005 • Genworth Financial (erstwhile GE Financial Services) 2005 – 2012 • Vangarde LLC (Hill AFB) 2012 – present

About me Positions Held • CAD Applications Developer (Calma, CADRA, Pro*Engineer) • Electronic Document Management System Manager (Oracle) • Visual Basic Application Developer (Oracle) • Systems Designer/Analyst (Oracle) • IT Project Manager (Oracle) • Data Analyst (Oracle) • SQL Server Database Administrator (Microsoft SQL Server) Other Factoids. • Married 38+ years • 3 children – UT (2) and MT (1) • 3 grandchildren • Interests: Cycling, Eating, Cooking, Baking, Time with Grandchildren, Movies…

Today’s Presentation: • Importance of Validating Backups • Database Restore Testing – Why do it? – Ways to do it? – SQL Agent Job(s) • Code Review

Who coined this saying? “You don’t have a backup until you’ve restored it. ” Kimberly Tripp Biography: • Taught for Microsoft University (SQL Server, Lan. Manager, Windows) – 1992 • SME & Technical Writer for Microsoft SQL Server Development Team – 1993 • President/Founder and owner of SQLskills. com – Since 1995 • Writer/Editor for SQL Server Magazine • Lecturer/Presenter at Microsoft Tech*Ed, SQLConnections, SQLPass, and other SQL Server-related events since 1996

Here’s her full quote: “You don’t have a backup until you’ve restored it. You don’t know whether the backup you just took was corrupt or not and will actually work in a disaster recovery situation. ” Kimberly Tripp http: //www. sqlskills. com/blogs/paul/importance-of-validating-backups/

Source: http: //www. sqlskills. com/blogs/paul/importance-of-validating-backups/

Can we guarantee that the backup file on disk (that we just validated) is good (i. e. , free from corruption)? Why not? • Traffic Control Analogy • An accident can happen at ANY time.

The BEST WAY to know our backups will restore? Frequent, actual database restore testing. Why do Database Restore Testing? • Continuity of service with current employer – Any “horror” stories out there? • Confidence in processes • Other reasons?

Some Ways to do Database Restore Testing. • Manual restore testing – Same server – Different server • Automated restore testing – Same server – Different server. Why? • Lack of space on same server • Security (Restore test prod DBs on dev server) • Other ways?

Automated Database Restore Testing using SQL Agent job(s) • Benefits of Automation. – Consistent schedule (same day/time each week) • Example Schedule: Do Production DBs once/week and Test/Dev DBs once/month – Best use of system resources (e. g. , on weekends) – No process variation (minimize error modes) – Instant notification of success/failure – Other benefits?

SQL Agent Job (same server) • Job Name: Restore Test User Databases • Job Steps 1. Restore the USER databases from the most recent FULL and DIFFERENTIAL backups 2. Do DBCC CHECKDB on newly-restored databases 3. Get date/time of last successful DBCC CHECKDB 4. Email DBCC CHECKDB results to DBA 5. Delete the restore-tested databases

1. Restore the USER databases from the most recent FULL and DIFFERENTIAL backups /* ================================== -- Name: Restore the user databases from the most recent FULL & DIFFERENTIAL (if applicable) backup files on the NAS. -- Author: Robert H. Every -- Create date: 10 July 2014 -- Description: This script gets the name of each user database on the SQL Server and then it restores the databases with new names using the most recent FULL backup and, if it exists, the most recent DIFFERENTIAL backup files. The name format of the restored database will be 'Restored_' + @Server. Name + '_' + @database. ************************************ ** Revision History ************************************ ** Date Author Description =============================== */ SET NOCOUNT ON

DECLARE DECLARE DECLARE DECLARE @Server. Name varchar(128) @database varchar(255) @Restored. Database. Name varchar(255) @Full. Backup. Path varchar(max) @Differential. Backup. Path varchar(max) @Drop. Database. Command nvarchar(max) @Restore. DBFrom. Full. BUCommand nvarchar(max) @Restore. DBFrom. Diff. BUCommand nvarchar(max) @linefeed CHAR(2) @name varchar(max) @physical_name varchar(max) @Dot. Position. From. Right int @Days. Since. Last. Full. Backup int @Days. Since. Last. Differential. Backup int @Recover. Database. Command nvarchar(max) DECLARE DECLARE @year varchar(4) @Month varchar(2) @Date. In. Month varchar(2) @Last. Saturday. Date datetime @Days. Since. Last. Saturday int @Date. Part. Of. Backup. File. Name nvarchar(max)

/* Set the @linefeed variable */ SET @linefeed = CHAR(13) + CHAR(10) /* Initialize variables. */ SET @Server. Name = '' SET @Restore. DBFrom. Full. BUCommand = '' SET @Restore. DBFrom. Diff. BUCommand = '' SET @Recover. Database. Command = '' /* Get the server name. */ select @Server. Name = convert(nvarchar(128), serverproperty('servername')); /* Get last Saturday's date. */ SET @Last. Saturday. Date = (dateadd(DD, -2, DATEADD(WW, DATEDIFF(WW, 0, getdate() 0))) /* Get select the year, month, and day of month data. */ @year = YEAR(@Last. Saturday. Date) @Month = MONTH(@Last. Saturday. Date) @Date. In. Month = DAY(@Last. Saturday. Date) ),

if LEN(@Month) = 1 SET @Month = '0' + @Month if LEN(@Date. In. Month) = 1 SET @Date. In. Month = '0' + @Date. In. Month /* Set the date part of last Saturday's backup file name. */ SET @Date. Part. Of. Backup. File. Name = @year + '_' + @Month + '_' + @Date. In. Month DECLARE Database. Cursor CURSOR FOR SELECT name FROM master. sys. databases sd WHERE sd. state = 0 -- state = 0 = ONLINE and sd. database_id > 4 -- User databases have IDs > 4 and sd. name not like 'Restored_' + '%' order by name

OPEN Data. Base. Cursor FETCH NEXT FROM Database. Cursor INTO @database WHILE @@FETCH_STATUS = 0 BEGIN /* Create the "Restored" database name. */ SET @Restored. Database. Name = 'Restored_' + @Server. Name + '_' + @database /* If the "Restored" database already exists, then drop it. */ if exists(select * from master. dbo. sysdatabases where name = @Restored. Database. Name) BEGIN SET @Drop. Database. Command = 'DROP DATABASE ' + '[' + @Restored. Database. Name + ']' EXECUTE master. dbo. sp_executesql @Drop. Database. Command END

/* Reinitialize variables. */ SET @Restore. DBFrom. Full. BUCommand = '' SET @Restore. DBFrom. Diff. BUCommand = '' SET @Full. Backup. Path = '' /* Get the path to the last Saturday's FULL backup of the database. */ SELECT top(1) @Days. Since. Last. Full. Backup = DATEDIFF(D, MAX(b. backup_finish_date), CURRENT_TIMESTAMP), @Full. Backup. Path = bm. physical_device_name FROM master. . sysdatabases a LEFT OUTER JOIN msdb. . backupset b ON a. name = b. database_name LEFT OUTER JOIN msdb. . backupmediafamily bm on b. media_set_id = bm. media_set_id WHERE a. name = @database and b. type = 'D' and -- Type D = FULL Backup b. backup_finish_date > (CURRENT_TIMESTAMP - 7) and bm. physical_device_name like '%' + @Server. Name + '%backup%' + @Date. Part. Of. Backup. File. Name + '%' GROUP BY a. name, b. type, bm. physical_device_name

/* Build the @Restore. DBFrom. Full. BUCommand command for the FULL backup file. */ SET @Restore. DBFrom. Full. BUCommand = @Restore. DBFrom. Full. BUCommand + 'RESTORE DATABASE [' + @Restored. Database. Name + ']' + @linefeed SET @Restore. DBFrom. Full. BUCommand = @Restore. DBFrom. Full. BUCommand + ' from DISK = ''' + @Full. Backup. Path + '''' + @linefeed SET @Restore. DBFrom. Full. BUCommand = @Restore. DBFrom. Full. BUCommand + ' with' + @linefeed /* Get the disk and folder path to the mdf, ldf, and any other database files. . . */ DECLARE Files_Cursor CURSOR FOR select name, physical_name -- name = logical name, physical_name = path to file. from sys. master_files where database_id = db_id(@database) OPEN Files_Cursor FETCH NEXT FROM Files_Cursor INTO @name, @physical_name While @@FETCH_STATUS = 0 BEGIN SET @Dot. Position. From. Right = CHARINDEX('. ', REVERSE(@physical_name), 1) SET @Restore. DBFrom. Full. BUCommand = @Restore. DBFrom. Full. BUCommand + ' move ''' + @name + ''' to ''' + SUBSTRING(@physical_name, 1, LEN(@physical_name) - @Dot. Position. From. Right) + '_Restored' + SUBSTRING(@physical_name, LEN(@physical_name) - @Dot. Position. From. Right + 1, 99) + ''', ' + @linefeed FETCH NEXT FROM Files_Cursor INTO @name, @physical_name END CLOSE Files_Cursor DEALLOCATE Files_Cursor

SET @Restore. DBFrom. Full. BUCommand = @Restore. DBFrom. Full. BUCommand + ' STATS = 1, replace, norecovery; ' + @linefeed /* Execute @Restore. DBFrom. Full. BUCommand command on all online databases on this SQL Server Instance. */ EXECUTE master. dbo. sp_executesql @Restore. DBFrom. Full. BUCommand /* Reinitialize variable(s) */ SET @Restore. DBFrom. Diff. BUCommand = ''

/* Get path to the most recent DIFFERENTIAL database backup. */ SELECT top(1) @Days. Since. Last. Differential. Backup = DATEDIFF(D, MAX(b. backup_finish_date), CURRENT_TIMESTAMP), @Differential. Backup. Path = bm. physical_device_name FROM master. . sysdatabases a LEFT OUTER JOIN msdb. . backupset b ON a. name = b. database_name LEFT OUTER JOIN msdb. . backupmediafamily bm on b. media_set_id = bm. media_set_id WHERE a. name = @database and b. type = 'I' and -- Type I = DIFFERENTIAL Backup b. backup_finish_date > (CURRENT_TIMESTAMP - 1) and bm. physical_device_name like '%' + @Server. Name + '%backup%‘ GROUP BY a. name, b. type, bm. physical_device_name, b. database_backup_lsn.

/* If the database does not have differential backups taken, then RECOVER the database. */ IF @Days. Since. Last. Differential. Backup is NULL -- No Differential Backup for this DB. BEGIN -- No Differential Backup for this DB. /* No Differential backup, then nothing to do in this block. */ print 'No Differential Backup for this DB. ' SET @Recover. Database. Command = '' END

ELSE -- This DB has a Differential Backup. BEGIN TRANSACTION BEGIN TRY print '-- Days since last DIFFERENTIAL backup: ' + cast(@Days. Since. Last. Differential. Backup as varchar) + @linefeed print '-- Path to last DIFFERENTIAL backup: ' + @Differential. Backup. Path + @linefeed /* Build the @Restore. DBFrom. Diff. BUCommand command. */ SET @Restore. DBFrom. Diff. BUCommand = @Restore. DBFrom. Diff. BUCommand + 'RESTORE DATABASE [' + @Restored. Database. Name + ']' + @linefeed SET @Restore. DBFrom. Diff. BUCommand = @Restore. DBFrom. Diff. BUCommand + ' from DISK = ''' + @Differential. Backup. Path + '''' + @linefeed SET @Restore. DBFrom. Diff. BUCommand = @Restore. DBFrom. Diff. BUCommand + ' with' + @linefeed

/* Must get the disk and folder path to the mdf, ldf, and any other database files. . . this could be tricky. */ DECLARE Files_Cursor CURSOR FOR select name, physical_name from sys. master_files where database_id = db_id(@database) OPEN Files_Cursor FETCH NEXT FROM Files_Cursor INTO @name, @physical_name While @@FETCH_STATUS = 0 BEGIN SET @Dot. Position. From. Right = CHARINDEX('. ', REVERSE(@physical_name), 1) SET @Restore. DBFrom. Diff. BUCommand = @Restore. DBFrom. Diff. BUCommand + ' move ''' + @name + ''' to ''' + SUBSTRING(@physical_name, 1, LEN(@physical_name) – @Dot. Position. From. Right) + '_Restored' + SUBSTRING(@physical_name, LEN(@physical_name) – @Dot. Position. From. Right + 1, 99) + ''', ' + @linefeed FETCH NEXT FROM Files_Cursor INTO @name, @physical_name END CLOSE Files_Cursor DEALLOCATE Files_Cursor

SET @Restore. DBFrom. Diff. BUCommand = @Restore. DBFrom. Diff. BUCommand + ‘ STATS = 1, replace, norecovery; ' + @linefeed /* Execute the Restore. DBFrom. Diff. BUCommand command on this database. */ EXECUTE master. dbo. sp_executesql @Restore. DBFrom. Diff. BUCommand /* Reinitialize variable(s) */ SET @Restore. DBFrom. Diff. BUCommand = '' SET @Days. Since. Last. Full. Backup = -1 SET @Days. Since. Last. Differential. Backup = NULL COMMIT TRANSACTION END TRY

/* RECOVER this database before going on to the next database. /* Build the @Recover. Database. Command command. */ SET @Recover. Database. Command = 'RESTORE DATABASE [' + @Restored. Database. Name + ']' + @linefeed SET @Recover. Database. Command = @Recover. Database. Command + ' with RECOVERY' + @linefeed /* Execute the @Recover. Database. Command command. */ EXECUTE master. dbo. sp_executesql @Recover. Database. Command /* Fetch the "next" result from the query into the cursor. */ FETCH NEXT FROM Database. Cursor INTO @database END /* Close and deallocate the database cursor. */ CLOSE Database. Cursor DEALLOCATE Database. Cursor */

2. /* ----- Do DBCC CHECKDB on newly-restored databases ================================== Name: Do DBCC CHECKDB on newly-restored databases Author: Robert H. Every Create date: 10 July 2014 Description: This script gets the name of each database on the SQL Server instance where the database name like 'Restored_' + @Server. Name + '%'. Then it builds a DBCC Check. DB command script. Then it executes the DBCC CHECKDB script. ************************************ ** Revision History ************************************ ** Date Author Description ** -----------------** dd-MMM-yyyy Reviser Name Goes Here Description goes here. . . ================================== */ SET NOCOUNT ON

DECLARE @Server. Name varchar(128) @database varchar(255) @DBCC_Check. DB_Command varchar(max) @linefeed CHAR(2) /* Set the @linefeed variable */ SET @linefeed = CHAR(13) + CHAR(10) /* Initialize variables. */ SET @Server. Name = '' SET @DBCC_Check. DB_Command = 'use master' + @linefeed /* Get the server name. */ select @Server. Name = convert(nvarchar(128), serverproperty('servername'));

DECLARE Database. Cursor CURSOR FOR SELECT name FROM master. sys. databases sd WHERE sd. state = 0 -- state = 0 = ONLINE. and name like 'Restored_' + @Server. Name + '%' order by name OPEN Data. Base. Cursor /* Fetch the "first" database name. */ FETCH NEXT FROM Database. Cursor INTO @database WHILE @@FETCH_STATUS = 0 BEGIN /* Build the @DBCC_Check. DB_Command command. */ SET @DBCC_Check. DB_Command = @DBCC_Check. DB_Command + 'DBCC CHECKDB ([' + @database + ']); ' + @linefeed /* Fetch the "next" database name. */ FETCH NEXT FROM Database. Cursor INTO @database END

/* Close and deallocate the database cursor. */ CLOSE Database. Cursor DEALLOCATE Database. Cursor /* Print the @DBCC_Check. DB_Command string just for kicks. */ print @DBCC_Check. DB_Command /* Execute the DBCC Check. DB command on all online databases on this SQL Server Instance. */ EXEC(@DBCC_Check. DB_Command) use master DBCC CHECKDB ([Restored_Server. Name_Database. Name-A]); . . . DBCC CHECKDB ([Restored_Server. Name_Database. Name-Z]);

3. /* ----- Get date/time of last successful DBCC CHECKDB ================================== Name: Get date/time of last successful DBCC CHECKDB execution Author: Robert H. Every Create date: 10 July 2014 Description: This script ensures that the master. dbo. DBCCRes table exists. If it doesn't, then the script creates it. If it does, then the script truncates it to ensure it contains no data. Then the script adds a row of data containing the date/time of the most recent successful DBCC CHECKDB that was performed on each database whose name is like 'Restored_' + @Server. Name + '%'. ************************************ ** Revision History ************************************ ** Date Author Description ** -----------------** dd-MMM-yyyy Reviser Name Goes Here Description goes here. . . ================================== */

/* Create a temporary table to store some DBCC data */ CREATE TABLE #temp ( Id INT IDENTITY(1, 1), Parent. Object VARCHAR(255), [OBJECT] VARCHAR(255), Field VARCHAR(255), [VALUE] VARCHAR(255) ) /* Ensure that the table dbo. master. dbo. DBCCRes exists. use master go IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA. TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'DBCCRes' ) */

/* The table does NOT exist. . . create it. */ Begin print 'Creating table dbo. master. dbo. DBCCRes' /* This is the permanent table from which the DBCC CHECKDB data will be selected later. */ CREATE TABLE master. dbo. DBCCRes ( Id INT IDENTITY(1, 1)PRIMARY KEY CLUSTERED, DBName sysname, dbcc. Last. Known. Good DATETIME, Row. Num INT ) End else /* The table exists. . . do NOT create it. Truncate it. */ Begin print 'The table master. dbo. DBCCRes exists. Truncating it. ' TRUNCATE TABLE master. dbo. DBCCRes End

DECLARE @DBName SYSNAME DECLARE @SQL VARCHAR(512) DECLARE @Server. Name varchar(128) /* Get the server name. */ select @Server. Name = convert(nvarchar(128), serverproperty('servername')); /* Get the names of “Restored” databases. */ DECLARE dbccpage CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR SELECT name FROM master. sys. databases WHERE state = 0 and -- state = 0 = ONLINE name like 'Restored_' + @Server. Name + '%';

OPEN dbccpage; FETCH NEXT FROM dbccpage INTO @DBName; -- Get name of “first” “restored” database. WHILE @@FETCH_STATUS = 0 BEGIN /* Put DBCC CHECKDB data into the #temp table. */ SET @SQL = 'Use [' + @DBName +']; ' +CHAR(10)+CHAR(13) SET @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'], 1, 9, 3) WITH TABLERESULTS; ' +CHAR(10)+CHAR(13) INSERT INTO #temp EXECUTE (@SQL); SET @SQL = '' /* Insert DB name and Last. Known. Good date/time data into DBCCRes table. */ INSERT INTO master. dbo. DBCCRes ( DBName, dbcc. Last. Known. Good, Row. Num ) SELECT @DBName, VALUE , ROW_NUMBER() OVER (PARTITION BY Field ORDER BY VALUE) AS Rownum FROM #temp WHERE field = 'dbi_dbcc. Last. Known. Good'; TRUNCATE TABLE #temp; -- Truncate #temp table preparatory for next DB. FETCH NEXT FROM dbccpage INTO @DBName; -- Get name of “next” online database. END

CLOSE dbccpage; DEALLOCATE dbccpage; /* Drop the #temp table. . . we're done with it. */ DROP TABLE #temp /* The next line is NOT part of the job step. */ /* Wanna see what’s in the DBCCRes table? */ SELECT * FROM [master]. [dbo]. [DBCCRes] WHERE Row. Num = 1 Id DBName dbcc. Last. Known. Good Row. Num 5 Restored_Server. Name_Report. Server 2016 -02 -19 12: 35: 00. 000 1 6 Restored_Server. Name_Report. Server. Temp. DB 2016 -02 -19 12: 35: 02. 000 1

4. Email DBCC CHECKDB results to DBA /* ----- ================================== Name: Email DBCC CHECKDB results to DBA operator Author: Robert H. Every Create date: 10 July 2014 Description: This script selects one row for each database from the master. dbo. DBCCRes table and emails the results to the DBA operator. The results are an email attachment. ************************************ ** Revision History ************************************ ** Date Author Description ** -----------------** dd-MMM-yyyy Reviser Name Goes Here Description goes here. . . ================================== */ /* Declare variables */ DECLARE @e. Mail. Msg. Body varchar(4000) DECLARE @linefeed CHAR(2)

/* Set the @linefeed variable */ SET @linefeed = CHAR(13) + CHAR(10) /* Create the email message. */ SET @e. Mail. Msg. Body = 'The attached succeeded on each DB. ' SET @e. Mail. Msg. Body = @e. Mail. Msg. Body file contains DB names and the last time a DBCC CHECKDB + @linefeed + 'Robert Every, FLMI, ACS' + @linefeed + '(800) 555 -1212' /* Send the mail. */ EXEC msdb. dbo. sp_send_dbmail @profile_name = 'SQL Alerts', @recipients = 'robert. every. ctr@us. af. mil', @subject = 'Restore-tested DB names and successful DBCC CHECKDB dates attached', @body = @e. Mail. Msg. Body, /* Write the query to get the database names and the date/time of the last successful DBCC CHECKDB was executed on each database. */ @query = 'SELECT DBName, dbcc. Last. Known. Good as [Successful Consistency Check] FROM master. dbo. DBCCRes WHERE Row. Num = 1; ' , @attach_query_result_as_file = 1 ;

Contents of the emailed file. DBName --------------------Restored_Server. Name_Report. Server. Temp. DB (23 rows affected) Successful Consistency Check --------------2016 -02 -19 12: 34: 59. 920 2016 -02 -19 12: 35: 01. 967

5. /* ----- Delete the restore-tested databases ================================== Name: Delete the restore-tested and consistency-checked databases Author: Robert H. Every Create date: 10 July 2014 Description: This script gets the name of each database on the SQL Server instance where the database name is like 'Restored_' + @Server. Name + '%'. . . Then it deletes (drops) each of these databases. ************************************ ** Revision History ************************************ ** Date Author Description ** -----------------** dd-MMM-yyyy Reviser Name Goes Here Description goes here. . . ================================== */ SET NOCOUNT ON

DECLARE DECLARE @Server. Name varchar(128) @database varchar(255) @Delete. Backup. History. Command varchar(max) @Set. Single. User. Command varchar(max) @Delete_DB_Command varchar(max) @linefeed CHAR(2) /* Set the @linefeed variable */ SET @linefeed = CHAR(13) + CHAR(10) /* Initialize variables. */ SET @Server. Name = '' SET @Delete_DB_Command = '' SET @Delete. Backup. History. Command = '' /* Get the server name. */ select @Server. Name = convert(nvarchar(128), serverproperty('servername'));

DECLARE Database. Cursor CURSOR FOR SELECT name FROM master. sys. databases sd WHERE sd. state = 0 -- state = 0 = ONLINE. and name like 'Restored_' + @Server. Name + '%' order by name

OPEN Data. Base. Cursor FETCH NEXT FROM Database. Cursor INTO @database WHILE @@FETCH_STATUS = 0 BEGIN /* Delete any backup history for this database. */ SET @Delete. Backup. History. Command = 'EXEC msdb. dbo. sp_delete_database_backuphistory ' + '''' + @database + '''' EXEC(@Delete. Backup. History. Command) /* Set this database to single-user mode. */ SET @Set. Single. User. Command = 'ALTER DATABASE [' + @database + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' EXEC(@Set. Single. User. Command ) /* Delete (i. e. , drop) this database. */ SET @Delete_DB_Command = 'DROP DATABASE [' + @database + ']' EXEC(@Delete_DB_Command) /* Reinitialize command variables. */ SET @Delete. Backup. History. Command = '' SET @Set. Single. User. Command = '' SET @Delete_DB_Command = ''

/* Fetch the "next" result from the query into the cursor. */ FETCH NEXT FROM Database. Cursor INTO @database END /* Close and deallocate the database cursor. */ CLOSE Database. Cursor DEALLOCATE Database. Cursor

Q&A