SQL Server Full Backup Verification

As database administrators, our first focus in our production environments should be backup and recovery.  I have mentioned this before in previous posts and it can never be said too much.  Sure, you can backup your database left and right, but unless you verify the backups with practice recovery or some other type of verification, your backup and recovery plan is unreliable.

SQL Server author Steve Rezhener (https://www.sqlservercentral.com/author/walkair) has just published a scripted backup verification process that I thought was interesting.  I tested this script on a SQL Server 2019 Developer Edition lab environment and wanted to pass this process on along with my comments.

This verification script performs the following actions:

  1. Gets the latest full backup of the user-designated database
  2. Checks if there is enough free space for the trial recovery of the user-designated database
  3. Restores the latest full backup into a copy database
  4. The copy database is checked for corruption
  5. The copy database is removed
  6. The check results are placed into a temporary table, published, then dropped

Here are the contents of the published script.

DECLARE @DBName   AS VARCHAR(100) = 'DemoDB'
DECLARE @SQLString NVARCHAR(500), @BackupFileName AS NVARCHAR(500)
DECLARE @ParmDefinition nvarchar(500)
DECLARE @Restore_DBName AS VARCHAR(100) = @DBName
DECLARE @DataFileSpaceTaken_GB AS INT, @FreeDriveSpace_GB AS INT
DECLARE @OriginalDataFileName AS VARCHAR(500), @OriginalLogFileName AS VARCHAR(500)-- just in case
IF EXISTS(SELECT database_id FROM sys.databases WHERE name=@DBName)
BEGIN
--sys.databases
 
PRINT '-- 1. GET LATEST BACKUP FILE FOR A DATABASE--'
SELECT TOP 1 @BackupFileName = bakfams.Physical_Device_Name
--bakfams.Physical_Device_Name
, @DataFileSpaceTaken_GB = masfiles.size/1024/1024/1024
, @FreeDriveSpace_GB = osvols.available_bytes/1024/1024/1024
, @OriginalDataFileName = masfiles.physical_name
, @OriginalLogFileName = masfiles2.physical_name
FROM sys.databases AS dats
INNER JOIN msdb.dbo.backupset AS baks    ON baks.database_name = dats.name
INNER JOIN msdb.dbo.backupmediafamily AS bakfams ON baks.media_set_id = bakfams.media_set_id
INNER JOIN sys.master_files AS masfiles    ON masfiles.database_id = dats.database_id AND masfiles.type_desc = 'ROWS'
CROSS APPLY sys.dm_os_volume_stats(masfiles.database_id, masfiles.file_id) AS osvols
LEFT OUTER JOIN sys.master_files AS masfiles2  ON masfiles2.database_id = dats.database_id AND masfiles2.type_desc = 'LOG'
WHERE 1=1
AND dats.name = @DBName
AND baks.[type]='D'
ORDER BY baks.backup_finish_date DESC
PRINT @BackupFileName
 
PRINT '-- 2. CREATE DATABASE NAME TO RESTORE --'
SET @Restore_DBName = @Restore_DBName + '_' + DATENAME(MONTH,GETDATE())
SET @Restore_DBName = @Restore_DBName + CONVERT(VARCHAR(2),DAY(GETDATE()))
SET @Restore_DBName = @Restore_DBName + CONVERT(VARCHAR(4),YEAR(GETDATE()))
PRINT @Restore_DBName

PRINT '-- 3. CHECK FREE DISKSPACE TO RESTORE THE DATABASE --'
PRINT @DataFileSpaceTaken_GB
PRINT @FreeDriveSpace_GB
IF (@FreeDriveSpace_GB<@DataFileSpaceTaken_GB*2) BEGIN PRINT '-- not enough space --'; RETURN; END
 
PRINT '-- 4. RESTORE DB--'
SET @SQLString = 'RESTORE DATABASE [' + @Restore_DBName + ']'
SET @SQLString = @SQLString + ' FROM DISK = N''' + @BackupFileName + ''''
SET @SQLString = @SQLString + ' WITH FILE = 1,'
SET @SQLString = @SQLString + ' MOVE N''' + @DBName + ''''
SET @SQLString = @SQLString + ' TO N''' + REPLACE(@OriginalDataFileName,@DBName,@Restore_DBName) + ''''
SET @SQLString = @SQLString + ', MOVE N''' + @DBName + '_log'''
SET @SQLString = @SQLString + ' TO N''' + REPLACE(@OriginalLogFileName,@DBName,@Restore_DBName) + ''''
SET @SQLString = @SQLString + ', NOUNLOAD, REPLACE, STATS = 10'
PRINT @SQLString
EXECUTE sp_executesql @SQLString
--RETURN
 
PRINT '-- 5. CHECK RESTORED DATABASE--'
SET @SQLString = 'DBCC CHECKDB ('+ @Restore_DBName + ')'
SET @SQLString = @SQLString + ' WITH NO_INFOMSGS ' -- WITH TABLERESULTS
EXECUTE sp_executesql @SQLString
 
PRINT '-- 6. DROP RESTORED DATABASE--'
SET @SQLString = 'DROP DATABASE ' + @Restore_DBName
EXECUTE sp_executesql @SQLString
 
PRINT '—-7. CREATE TEMP winlog TABLE --'
IF OBJECT_ID('tempdb..#winlog') !=0 DROP TABLE #winlog
CREATE TABLE #winlog
(
rowID  INT IDENTITY(1,1)
, LogDate  DATETIME
, ProcessInfo  VARCHAR(50)
, textRow  VARCHAR(4000)
)
PRINT  '-- 8. STORE DBCC CHECKDB RESULTS --'
INSERT INTO #winlog(LogDate, ProcessInfo, textRow)
EXEC master.dbo.xp_readerrorlog
PRINT  '-- 9. LOCATE LAST/FIRST ROWID —-'
DECLARE @textRow nvarchar(500), @1stRowID AS INT = 0
SET @SQLString = 'SELECT TOP 1 @x1stRowID = rowID'
SET @SQLString = @SQLString + ' FROM #winlog'
SET @SQLString = @SQLString + ' WHERE textRow = ''Starting up database ''''' + @Restore_DBName + '''''.'''
SET @SQLString = @SQLString + ' ORDER BY rowID DESC'
SET @ParmDefinition = N'@x1stRowID AS INT OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @x1stRowID = @1stRowID OUTPUT;
SET @SQLString = 'SELECT *'
SET @SQLString = @SQLString + ' FROM #winlog'
SET @SQLString = @SQLString + ' WHERE RowID >= @xRowID'
SET @SQLString = @SQLString + ' ORDER BY rowID DESC'
PRINT 'SELECT FROM WINLOG: ' + @SQLString
 
PRINT '-- 10. DISPLAY RESULTS--'
SET @ParmDefinition = N'@xRowID INT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @xRowID = @1stRowID
DROP TABLE #winlog
END


When executed in my lab environment, this is what I saw (the latest entries are first):

rowID LogDate ProcessInfo textRow
105 2019-11-21 07:37:28.030 spid56 DBCC CHECKDB (Hospital_November212019) WITH no_infomsgs 
executed by WIN-NVFHQDPGP9D\Administrator found 0 errors and repaired 0 errors. 
Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split 
point LSN = 00000022:00000b2f:0001 and first LSN = 00000022:00000b2d:0001. 

104 2019-11-21 07:37:27.810 Backup RESTORE DATABASE successfully processed 449 pages 
in 0.029 seconds (120.740 MB/sec).

103 2019-11-21 07:37:27.780 Backup Database was restored: Database: Hospital_November212019, 
creation date(time): 2019/01/03(14:07:43), first LSN: 34:2822:1, last LSN: 34:2825:1, 
number of dump devices: 1, device information: (FILE=1, TYPE=DISK: 
{'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Hospital.bak'}). 
Informational message. No user action required.

102 2019-11-21 07:37:27.760 Backup Restore is complete on database 'Hospital_November212019'. The database 
is now available.

101 2019-11-21 07:37:27.620 spid56 Parallel redo is shutdown for database 'Hospital_November212019' 
with worker pool size [1].

100 2019-11-21 07:37:27.530 spid56 Parallel redo is started for database 'Hospital_November212019' 
with worker pool size [1].

99 2019-11-21 07:37:27.500 spid56 Starting up database 'Hospital_November212019'.



Note that this script only works for full and not incremental backups.  The script needs to be able to find at least one full backup or it will fail.