A popular joke about DBAs and database backups goes like this. There are two types of DBAs:
- DBAs that do backups
- 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.
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.
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.
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.
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/