We have all heard that backup is not complete until you test it by restoring it.  We also know that part of a DR plan involves the testing of those backups through restores.

I recently had the opportunity to run through a test scenario and ran into a substantial failure.  It was nearly catastrophic due to the size of the database, size of the backups and location of the DR site where we were testing the recovery.  Let’s just fast forward to all of the fun rather than bore you with all of the minutia.

I began the test with the restore of the full database backup.  This restore took in excess of 16 hours.  Once complete, the next phase was to restore the latest differential and then log backups from there.

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This is where the fun began.  I sat there pondering this for a bit.  How could this be the case?  I verified that the Full backup was only run once via the regular schedule.  I even ran the following to script to check.

Code block

DECLARE @SQLVer SQL_VARIANT
,@DBName VARCHAR(128)
,@NumDaysSMALLINT
 
SET @DBName = 'YourDatabaseNameHere'
;
SET @NumDays = 14
;
SET @SQLVer = SERVERPROPERTY('ProductVersion');
 
SELECT a.database_name,a.backup_start_date
,b.physical_device_name AS BackupPath
,a.position
,a.type
,a.backup_size/1024/1024 AS BackupSizeMB
,CASE 
WHEN CONVERT(INT,LEFT(CONVERT(VARCHAR(12),@SQLVer),2)) < 10 
THEN 0
ELSE a.compressed_backup_size/1024/1024 
END AS CompressedBackMB
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.type IN ('D','I')
And a.backup_start_date > GETDATE()- @NumDays
And b.physical_device_name not like '%{%'
AND a.database_name = ISNULL(@DBName,a.database_name)
ORDER BY a.database_name,a.backup_start_date;
 
GO

This code returned only the one full backup that I knew and expected should be there from within the past few days.  So I tried that differential restore again.  I also tried a new differential just in case.  All of that was futile.  Then I looked back to my script and removed the following line

Code block

And b.physical_device_name not like '%{%'

Tada – that revealed the problem.  Suddenly I had a new full backup appear for each day since the full backup that was taken through the regularly scheduled means.  Each new backup had the following nomenclature for the backup path.

Code block

{39629103-33E4-4D97-893D-560029822118}19

And then it became apparent what was happening.  I had been excluding backups with paths following that type of naming convention due to CommVault in the past.  In this case I knew that CommVault was not a part of the picture.  I did know however that SAN snapshots were being taken on a nightly basis.

This was the aha moment – SAN snapshotting was causing a full backup to be registered in SQL Server.  And it makes perfect sense when looking at the backup history and sizes for each of the differentials that had been taken.  The differential backups were almost always the same size.  If you are running SAN Snapshots, you may want to reconsider your differential backup plan.

This script (just the revised version of the above) will help list the Full and Differential backups that have been taken.

Code block

DECLARE @SQLVer SQL_VARIANT
,@DBName VARCHAR(128)
,@NumDaysSMALLINT
 
SET @DBName = 'YourDatabaseNameHere'
;
SET @NumDays = 14
;
SET @SQLVer = SERVERPROPERTY('ProductVersion');
 
SELECT a.database_name,a.backup_start_date
,b.physical_device_name AS BackupPath
,a.position
,a.type
,a.backup_size/1024/1024 AS BackupSizeMB
,CASE 
WHEN CONVERT(INT,LEFT(CONVERT(VARCHAR(12),@SQLVer),2)) < 10 
THEN 0
ELSE a.compressed_backup_size/1024/1024 
END AS CompressedBackMB
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.type IN ('D','I')
And a.backup_start_date > GETDATE()- @NumDays
AND a.database_name = ISNULL(@DBName,a.database_name)
ORDER BY a.database_name,a.backup_start_date;
 
GO