How does the script work?
- For every database retrives list of backups
- Finds files that are needed to make full current restore. It find latest full backup then latest differential backup and all log backups after that
- Checks if latest backup has been made within definied time (last 25 hours in my configuration).
- Tries to restore database to temporary database using RESTORE DATABASE and RESTORE LOG. It also moves all database files to specified folder, so I can make restoration test on another disk than production database.
- Calls DBCC CHECKDB and checks if restored database is healthy.
Example of "Recovery test failed" email:
Example of "Recovery test success" email:
Finally this script allows me to find out:
- Do all of my databases were backed up within last X hours? (loop through all databases, not only backup files)
- Do all files that are required to recovery database are available in Azure Blob storage? (recovery test)
- Is backup consistent? (dbcc checkdb)
In my scenario this script is run just after the backup script. Just remember - if you don't get an email with success/error it's also an error indicator.
Source code
/*
SQL Sever 2012 CU2 Azure Blob backup recovery test script
(C) 2013 Marek Malachowski, PricePanorama.com
*/
-- configuration lines
DECLARE @maximumBackupAge int=25; -- hours. Will log an error if lastest backup is older
DECLARE @azureCredential varchar(20)='mycredential'
DECLARE @tempFolder varchar(100)='E:\Backups\' -- must end with "\" and must exists
DECLARE @recoveryTestDbName varchar(100)='Test_Recovery'; -- database will be created, recovered and deleted. DO NOT use existing database name.
DECLARE @emailRecipients varchar(max)='****@*****.com'; -- semicolon delimited
DECLARE @sqlMailProfile varchar(100)='recoveryTester';
-- code
DECLARE @BackupErrors AS Table (dbname VARCHAR(50), msg VARCHAR(200))
DECLARE @name VARCHAR(50), @recoveryMode VARCHAR(20)
DECLARE @finalEmailHtml VARCHAR(max)=' ';
DECLARE @BackupFiles AS Table (fileurl varchar(259), filetype char(1), backupstartdate datetime)
DECLARE @dbFiles AS Table (logicalFileName varchar(200), filetype char(1))
-- must declare exactly as the RESTORE FILELISTONLY result
DECLARE @fileTable table (
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileId tinyint,
CreateLSN numeric(25,0),
DropLSN numeric(25, 0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
TDEThumbprint varbinary(32)
)
DECLARE @dbccTable TABLE (
Error int NULL,
[Level] int NULL,
State int NULL,
MessageText nvarchar(2048) NULL,
RepairLevel nvarchar(22) NULL,
Status int NULL,
DbId int NULL,
DbFragId int NULL,
ObjectId int NULL,
IndexId int NULL,
PartitionId bigint NULL,
AllocUnitId bigint NULL,
RidDbId smallint NULL,
RidPruId smallint NULL,
[File] smallint NULL,
Page int NULL,
Slot int NULL,
RefDbId smallint NULL,
RefPruId smallint NULL,
RefFile smallint NULL,
RefPage int NULL,
RefSlot int NULL,
Allocation smallint NULL
)
DECLARE db_cursor CURSOR FOR
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') AND name not like 'Test_%' -- exclude these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name, @recoveryMode
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Backup_Filename AS VARCHAR (259)=''; --sql server device name length limit
DECLARE @FullSize bigint=0, @FullDate datetime, @FullUrl varchar(300)=null
DECLARE @DiffSize bigint=0, @DiffDate datetime, @DiffUrl varchar(300)=null
DECLARE @LogSize bigint=0, @LogDate datetime, @LogUrl varchar(300)=null
DELETE FROM @BackupFiles;
DELETE FROM @dbFiles;
-- Last full backup
SELECT TOP 1 @FullUrl=m.physical_device_name,@FullSize=s.backup_size,@FullDate=s.backup_start_date
FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name=@name AND s.[type]='D' ORDER BY backup_start_date DESC
IF @FullUrl IS NULL
BEGIN
INSERT INTO @BackupErrors VALUES (@name,'There is no FULL backup. Recovery test aborted!');
BREAK
END
ELSE
INSERT INTO @BackupFiles VALUES (@FullUrl,'D',@FullDate);
-- Last differential backup
SELECT TOP 1 @DiffUrl=m.physical_device_name,@DiffSize=s.backup_size,@DiffDate=s.backup_start_date
FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name=@name AND s.[type]='I' AND backup_start_date>=@FullDate ORDER BY backup_start_date DESC
IF NOT @DiffUrl IS NULL
INSERT INTO @BackupFiles VALUES (@DiffUrl,'I',@DiffDate);
-- All transaction log backups
DECLARE log_cursor CURSOR FOR
SELECT m.physical_device_name,s.backup_size,s.backup_start_date
FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name=@name AND s.[type]='L' AND backup_start_date>=
(SELECT MAX(backupDate) FROM (VALUES(@FullDate),(@DiffDate)) AS AllDates(backupDate))
ORDER BY backup_start_date ASC
OPEN log_cursor
FETCH NEXT FROM log_cursor INTO @LogUrl, @LogSize, @LogDate
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @BackupFiles VALUES (@LogUrl,'L',@LogDate);
FETCH NEXT FROM log_cursor INTO @LogUrl, @LogSize, @LogDate
END
CLOSE log_cursor
DEALLOCATE log_cursor
IF Datediff(hour,(select max(backupstartdate) from @BackupFiles),GETDATE())>@maximumBackupAge
INSERT INTO @BackupErrors VALUES (@name,'Latest backup is older than ' + cast(@maximumBackupAge as varchar(5)) + ' hours');
select * from @BackupFiles;
-- restore test here
DECLARE @fileurl varchar(259)='';
DECLARE @filetype char(1)='';
DECLARE files_cursor CURSOR FOR
select fileurl, filetype from @BackupFiles ORDER BY backupstartdate
OPEN files_cursor
FETCH NEXT FROM files_cursor INTO @fileurl, @filetype
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
DECLARE @recoveryCmd varchar(1000)='', @moveOptions varchar(1000)='';
DELETE FROM @fileTable;
INSERT @fileTable EXEC ('restore filelistonly from url = ''' + @fileurl + ''' WITH CREDENTIAL=''' + @azureCredential + '''');
-- restore full backup first, with move etc
IF @filetype='D'
BEGIN
SET @recoveryCmd = 'RESTORE DATABASE ' + @recoveryTestDbName + ' FROM URL=''' + @fileurl + ''' WITH ';
SELECT @moveOptions=(SELECT 'MOVE ''' + T.LogicalName + ''' TO ''' + @tempFolder + T.LogicalName + '.' + CASE T.Type WHEN 'D' THEN 'mdf' WHEN 'L' THEN 'ldf' END + ''',' AS [text()] FROM @fileTable T FOR XML PATH (''));
SET @recoveryCmd = @recoveryCmd + @moveOptions + ' NORECOVERY, CREDENTIAL=''' + @azureCredential + '''';
PRINT @recoveryCmd;
EXEC (@recoveryCmd);
END
-- differential backup doesn't neeed move
IF @filetype='I'
BEGIN
SET @recoveryCmd = 'RESTORE DATABASE ' + @recoveryTestDbName + ' FROM URL=''' + @fileurl + ''' WITH NORECOVERY, CREDENTIAL=''' + @azureCredential + '''';
PRINT @recoveryCmd;
EXEC (@recoveryCmd);
END
-- differential backup doesn't neeed move
IF @filetype='L'
BEGIN
SET @recoveryCmd = 'RESTORE LOG ' + @recoveryTestDbName + ' FROM URL=''' + @fileurl + ''' WITH NORECOVERY, CREDENTIAL=''' + @azureCredential + '''';
PRINT @recoveryCmd;
EXEC (@recoveryCmd);
END
END TRY
BEGIN CATCH
INSERT INTO @BackupErrors VALUES (@name,'Recovery of file ' + @fileurl + ' failed. ' + ERROR_MESSAGE());
END CATCH
FETCH NEXT FROM files_cursor INTO @fileurl, @filetype
END
CLOSE files_cursor
DEALLOCATE files_cursor
DECLARE @dbccResult varchar(max)='';
BEGIN TRY
-- bring database online
EXEC ('RESTORE DATABASE ' + @recoveryTestDbName + ' WITH RECOVERY');
-- check restore result. NOINDEX, as wee need performance, and indexes can be rebuild.
DELETE FROM @dbccTable;
INSERT INTO @dbccTable EXEC('DBCC CHECKDB (' + @recoveryTestDbName + ', NOINDEX) WITH TABLOCK, TABLERESULTS');
-- Error 8989 is final result, not always an error so we have to match text message
IF EXISTS (SELECT * FROM @dbccTable WHERE Error = 8989 AND MessageText NOT LIKE '%0 allocation errors and 0 consistency errors%')
BEGIN
INSERT INTO @BackupErrors SELECT @name,MessageText FROM @dbccTable WHERE Error = 8989;
END
ELSE
SELECT @dbccResult=Isnull((Select top 1 MessageText From @dbccTable WHERE Error = 8989), '
There is no DBCC CHECKDB final result
');
END TRY
BEGIN CATCH
INSERT INTO @BackupErrors VALUES (@name,'Recovery of database failed. ' + ERROR_MESSAGE());
END CATCH
-- current backup file liest
SET @finalEmailHtml = @finalEmailHtml + '
Database: ' + @name + '
Recovery file list:
' +
isNull(CAST ((SELECT fileurl FROM @BackupFiles FOR XML PATH('br'), TYPE ) AS NVARCHAR(MAX)),'No backup files for this database');
-- error list
IF EXISTS (SELECT * FROM @BackupErrors WHERE dbname=@name)
BEGIN
SET @finalEmailHtml = @finalEmailHtml + '
Backup recovery error list
' +
isNull(CAST ( ( SELECT li = msg, '' FROM @BackupErrors WHERE dbname=@name FOR XML PATH('ul'), TYPE ) AS NVARCHAR(MAX)), 'No errors for this database');
END
-- final dbcc result
SET @finalEmailHtml = @finalEmailHtml + @dbccResult;
-- drop test database and it's files, so next recovery can go
EXEC('DROP DATABASE ' + @recoveryTestDbName + ';');
FETCH NEXT FROM db_cursor INTO @name, @recoveryMode
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- sending an email
IF EXISTS (SELECT * FROM @BackupErrors)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients=@emailRecipients,
@subject = '[ERROR] SQL Server backup/recovery failed',
@body = @finalEmailHtml,
@importance = 'High',
@profile_name = @sqlMailProfile,
@body_format = 'HTML' ;
END
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients=@emailRecipients,
@subject = 'SQL Server backup/recovery success. Lucky you :)',
@body = @finalEmailHtml,
@profile_name = @sqlMailProfile,
@body_format = 'HTML' ;
END