Tag Archives: Backup Testing

Script for automatically testing SQL backups

Recently, I have spent some time writing a custom stored procedure that automates the process of restoring a copy of a database, running CHECKDB against the copy, then dropping the database.

That can be quite a tedious process if you have more than a few servers.

This script will be perfect if you have a DR server where backups are housed. Configure the paths on lines 90, 92, and 93. That is the only customization required. You can then schedule your backups to be tested for corruption. See the code below for more information.

Edit:  The code is not properly highlighted on WordPress.  However, I have tested the code and it works.

USE master;
GO

/*
Gary Gwyn
2/10/14
Create logging table for retaining a restore history
This table will be created when this script is ran the initial time.

During every step of the AutoRestore procedure, events are logged to the RestoreLog table.

I capture the following:
dbfilename: The Windows file name of the backup file that was restored
dbname: The name of the database that was restored by the stored proc
restoreStart: The time stamp when the restore began
restoreEnd: The time stamp when the restore completed
restoreStatus: Successful or Failed
checkDBStart: The time stamp when the checkDB began
checkDBEnd: The time stamp when the checkDB completed
checkDBStatus: Successful or Failed

*/
IF EXISTS (
 SELECT *
 FROM sys.objects
 WHERE object_id = OBJECT_ID(N'[dbo].[RestoreLog]')
 AND type IN (N'U')
 )
 DROP TABLE [dbo].[RestoreLog]
GO

CREATE TABLE dbo.RestoreLog (
 log_id INT identity(1, 1) PRIMARY KEY CLUSTERED
 ,dbfilename VARCHAR(150)
 ,dbname VARCHAR(50)
 ,restoreStart DATETIME
 ,restoreEnd DATETIME
 ,restoreStatus VARCHAR(12)
 ,checkDBStart DATETIME
 ,checkDBEnd DATETIME
 ,checkDBStatus VARCHAR(12)
 )
GO

IF EXISTS (
 SELECT *
 FROM sys.objects
 WHERE object_id = OBJECT_ID(N'[dbo].[usp_AutoRestore]')
 AND type IN (N'P', N'PC')
 )
 DROP PROCEDURE [dbo].[usp_AutoRestore]
GO

CREATE PROCEDURE dbo.usp_AutoRestore @DBtoLocate VARCHAR(25)
AS
/*
Gary Gwyn
2/10/14

Install this stored procedure on the SQL Server where you want to restore and test backups.
You can schedule this procedure to run for named databases.

USAGE: exec master..AutoRestore 'msdb'
The above example command will run the stored procedure against the msdb database.

The procedure will do the following:
Locate the most recent full backup of the named database (passed as parameter to SP).
Restore the most recent full backup as "DR_copy"
Run DBCC CheckDB against "DR_copy"
Drop "DR_copy"

At any point of failure, the stored procedure generates an error which will cause the scheduled job to fail and send a notification.

*/
DECLARE @Database VARCHAR(20);

SELECT TOP 1 @Database = NAME
FROM sys.databases
WHERE NAME LIKE '%' + @DBtoLocate + '%'
ORDER BY database_id

/************************************************
******
*** Configure these directories:::
*/
--This is the location where the SQL backup files are stored. Customize as needed.
DECLARE @Directory VARCHAR(100) = 'F:\mssql\backup\' + @@Servername + '\' + @Database + '\FULL';
--These are the directories where the restored database will be housed temporarily during the CheckDB stage. Customize as needed.
DECLARE @DestinationData VARCHAR(15) = 'D:\MSSQL\Data\DR_copy.mdf'
DECLARE @DestinationLog VARCHAR(15) = 'D:\MSSQL\Log\DR_copy.ldf'

/**************
End Configuration
***************/
-- Step 1 -- Locate most recent full backup
-- Credit to Patrick Keisler for providing help with the use of xp_dirtree.
-- http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html
IF OBJECT_ID('tempdb..#FileList') IS NOT NULL
 DROP TABLE #FileList;

CREATE TABLE #FileList (
 id INT IDENTITY(1, 1)
 ,subdirectory NVARCHAR(512)
 ,depth INT
 ,isfile BIT
 );

INSERT #FileList (
 subdirectory
 ,depth
 ,isfile
 )
EXEC master.sys.xp_dirtree @Directory
 ,1
 ,1;

DECLARE @file_to_restore VARCHAR(150);

SELECT TOP 1 @file_to_restore = subdirectory
FROM #FileList
WHERE isfile = 1
 AND RIGHT(subdirectory, 4) = '.BAK'
ORDER BY subdirectory DESC;

DECLARE @RecordID INT;

-- If a file was located, create a new record in the RestoreLog table. Else, exit the stored procedure with an error.
IF @file_to_restore IS NOT NULL
BEGIN
 INSERT INTO master..RestoreLog (
 dbfilename
 ,dbname
 )
 VALUES (
 @file_to_restore
 ,@Database
 )

SET @RecordID = scope_identity();
END
ELSE
BEGIN
 INSERT INTO master..RestoreLog (
 dbfilename
 ,dbname
 ,restoreStatus
 ,checkDBStatus
 )
 VALUES (
 'Does not exist'
 ,@Database
 ,'Failed'
 ,'Failed'
 )

PRINT error_message();

RAISERROR (
 'Backup for database could not be located'
 ,16
 ,1
 );

RETURN
 /**** Exit stored procedure *****/
END

DECLARE @full_path VARCHAR(250);

SET @full_path = @Directory + '\' + @file_to_restore;

-- Step 2 -- Restore full backup as DR_copy
-- Update the RestoreLog table
UPDATE master..RestoreLog
SET restoreStart = getdate()
WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID

BEGIN TRY
 -- Logical file name extraction: great stuff via stackoverflow: http://stackoverflow.com/questions/7089627/how-can-i-retrieve-the-logical-file-name-of-the-database-from-backup-file
 DECLARE @Table TABLE (
 LogicalName VARCHAR(128)
 ,[PhysicalName] VARCHAR(128)
 ,[Type] VARCHAR
 ,[FileGroupName] VARCHAR(128)
 ,[Size] VARCHAR(128)
 ,[MaxSize] VARCHAR(128)
 ,[FileId] VARCHAR(128)
 ,[CreateLSN] VARCHAR(128)
 ,[DropLSN] VARCHAR(128)
 ,[UniqueId] VARCHAR(128)
 ,[ReadOnlyLSN] VARCHAR(128)
 ,[ReadWriteLSN] VARCHAR(128)
 ,[BackupSizeInBytes] VARCHAR(128)
 ,[SourceBlockSize] VARCHAR(128)
 ,[FileGroupId] VARCHAR(128)
 ,[LogGroupGUID] VARCHAR(128)
 ,[DifferentialBaseLSN] VARCHAR(128)
 ,[DifferentialBaseGUID] VARCHAR(128)
 ,[IsReadOnly] VARCHAR(128)
 ,[IsPresent] VARCHAR(128)
 ,[TDEThumbprint] VARCHAR(128)
 )
 DECLARE @LogicalNameData VARCHAR(128)
 ,@LogicalNameLog VARCHAR(128)

INSERT INTO @table
 EXEC (
 '
RESTORE FILELISTONLY
FROM DISK = ''' + @full_path + '''
'
 )

SET @LogicalNameData = (
 SELECT LogicalName
 FROM @Table
 WHERE Type = 'D'
 )
 SET @LogicalNameLog = (
 SELECT LogicalName
 FROM @Table
 WHERE Type = 'L'
 )

RESTORE DATABASE [DR_copy]
 FROM DISK = @full_path
 WITH FILE = 1
 ,MOVE @logicalnamedata TO @DestinationData
 ,MOVE @logicalnamelog TO @DestinationLog
 ,NOUNLOAD
 ,STATS = 10

-- Update the RestoreLog table
 UPDATE master..RestoreLog
 SET restoreEnd = getdate()
 ,restoreStatus = 'Successful'
 WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID
END TRY

-- If there was an error with the restore, then exit the stored procedure.
BEGIN CATCH
 PRINT error_message();

-- Update the RestoreLog table
 UPDATE master..RestoreLog
 SET restoreEnd = getdate()
 ,restoreStatus = 'Failed'
 WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID

RAISERROR (
 'Restore database step failed'
 ,16
 ,1
 );

RETURN
 /***** Exit stored procedure ******/
END CATCH

-- Step 3 -- Run DBCC CheckDB against DR_copy
-- Update the RestoreLog table
UPDATE master..RestoreLog
SET checkDBStart = getdate()
WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID

BEGIN TRY
 --The DBCC CheckDB command
 DBCC CHECKDB ([DR_copy])
 WITH NO_INFOMSGS
 ,ALL_ERRORMSGS
 ,DATA_PURITY

-- Update the RestoreLog table
 UPDATE master..RestoreLog
 SET checkDBEnd = getdate()
 ,checkDBStatus = 'Successful'
 WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID
END TRY

-- If there was an error with the checkDB command execution, then generate an error and exit the stored procedure.
BEGIN CATCH
 PRINT error_message();

-- Update the RestoreLog table
 UPDATE master..RestoreLog
 SET checkDBEnd = getdate()
 ,checkDBStatus = 'Failed'
 WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID

RAISERROR (
 'DBCC CheckDB step failed'
 ,16
 ,1
 );

RETURN
 /*** exit stored procedure ****/
END CATCH

-- Step 4 -- Drop DR_copy
IF EXISTS (
 SELECT NAME
 FROM sys.databases
 WHERE NAME = 'DR_copy'
 )
 DROP DATABASE DR_copy
GO

Advertisements
Tagged , , ,