Introduction

A popular joke about DBAs and database backups goes like this. There are two types of DBAs:

1. DBAs that do backups
2. DBAs that will do backups

The joke is only stating the obvious – lack of database backups might be the end of a DBA career and/or the supported business. Unfortunately, that joke fails to mention database backups with corrupted data. Corrupted data in tables that are not accessed frequently could go unnoticed for a long time and could cause even bigger problems than not doing backups.

A long time ago, when SQL Server 2008 was the current version, I was working for an online media company as a Data Warehouse Analyst and an Accidental DBA. I was tasked to create a media content usage report for one of the major music labels. I was running some trivial select statements and to my surprise was greeted with a possible data corruption error. I immediately ran DBCC CHECKDB and confirmed the table corruption.

The bad news is, the table corruption happened on index=1, which is the clustered index. I tried the standard steps of recovering from that error, trying to fix the table with DBCC CHECKTABLE and restore that table from the latest database backup. In the end, I discovered that all this time I was backing up a database with a corrupted table.

The moral of this story is doing backups is only one-half of the DBA job. This is akin to not knowing the details of your automobile insurance policy. If you are not fully aware of your coverage, you are paying to only discover what was not covered at the time of an accident and pay full price for the consequences.

The Problem

Unfortunately, validating backups is not as trivial as database backups and restores. It would be nice to be able to test and validate your database backups, but the native maintenance plan has no tool for that, right? Additionally, it would be nice to routinely schedule such a mechanism that would make sure that database backups could be restored and those database backups are worth restoring, right? Look no further than the script below.

The Solution

To make this solution automated and repeatable, we are going to be building T-SQL strings and passing those to sp_execute stored procedure to run and capture results. We will have it to repeat that manual for every step. Additionally, due to a popular demand of having 2 different servers (one for backup and one for restore), I introduced a change to my initial all-in-one server script and now there is an option to have different servers.

At first, we need to understand if the backup and restore servers are actually the same server (compare @RemoteServer parameter to @@ServerName current system server). If different, we will create a linked server to the backup from the restore server. As a precautionary step, we will make sure that both database and backup exist. If yes, we will look for the latest full back of the selected database. We will then take @DBName and run it against some MSDB tables and DMVs. We will then create a database clone name (original database name + today's date) and use both the new name and backup file to restore a new database from a backup file (before the actual restore, we will make sure that there is enough disk space). In the next step, we will check the restored database against any data corruption, capture results and drop the cloned database.

The Code

I have this code wrapped in a stored procedure that I call routinely to validate that all my user databases are worth the restore time.

-- EXEC usp_VerifyDB @DBName = 'backuptestdb', @RemoteServer='.'
IF EXISTS (SELECT 1 FROM sys.procedures WHERE Name = 'usp_VerifyDB') DROP PROCEDURE usp_VerifyDB
GO
CREATE PROCEDURE usp_VerifyDB (@DBName AS VARCHAR(100), @RemoteServer AS VARCHAR(100) NULL) AS 
BEGIN
PRINT '-- STEP -1. CREATE A LINKED SERVER TO REMOTE/LOCAL SERVER --'
IF @RemoteServer IS NULL SET @RemoteServer = @@SERVERNAME-- if empty, replace it with a local server name
 
IF NOT EXISTS (SELECT srvid FROM sys.sysservers WHERE srvname = 'backupserver') 
EXEC sp_addlinkedserver @server = 'backupserver', @provider ='SQLNCLI', @datasrc = @RemoteServer, @srvproduct = 'SQL'

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
PRINT '-- 0. CHECK THAT BOTH DATABASE AND BACKUP EXIST --'
IF EXISTS(SELECT database_id FROM sys.databases as ses INNER JOIN msdb.dbo.backupfile AS ile ON ile.logical_name = ses.name WHERE ses.name=@DBName)
BEGIN
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 backupserver.master.sys.databases AS dats
INNER JOIN backupserver.msdb.dbo.backupset AS baks    ON baks.database_name = dats.name
INNER JOIN backupserver.msdb.dbo.backupmediafamily AS bakfams ON baks.media_set_id = bakfams.media_set_id
INNER JOIN backupserver.master.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
CROSS APPLY master.sys.dm_os_volume_stats(1, 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
ORDER BY bakfams.media_set_id DESC
PRINT @BackupFileName
 
PRINT '-- 2. CREATE DATABASE NAME TO RESTORE --'
SET @Restore_DBName = @Restore_DBName + '_' + DATENAME(MONTH,GETDATE())
SET @Restore_DBName = @Restore_DBName + LEFT('0' + CONVERT(VARCHAR(2),DAY(GETDATE())),2)
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(CAST(@OriginalDataFileName as nvarchar(max)),CAST(@DBName as nvarchar(max)),CAST(@Restore_DBName as nvarchar(max))) + ''''
SET @SQLString = @SQLString + ', MOVE N''' + @DBName + '_log'''
SET @SQLString = @SQLString + ' TO N''' + REPLACE(CAST(@OriginalLogFileName as nvarchar(max)),cast(@DBName as nvarchar(max)),cast(@Restore_DBName as nvarchar(max))) + ''''
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
ELSE 
BEGIN
PRINT '-- DATABASE IS NOT AVAILABLE OR HAVE NO BACKUP! --'
END
END

Running the above procedure should produce the following output.

Disclaimer

I was planning to post this blog 9+ years ago. I finally did it.

Originally posted on https://datasteve.com/2019/09/11/verify-your-database-backups/