verifiedIntroduction

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.

Long-time ago, when SQL Server 2008 was the thing, I was working for an online media company as a DataWarehouse 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 (tried to fix the table with DBCC_CHECKTABLE and restore that table from the latest database backup), to only discover that all this time I was backing up a database with a corrupted table.

The moral of this story – doing backups is only one-half of the DBA job and akin to not knowing 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.

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 a script below.

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.

At first, we need to find the latest full back of the selected database. We take @DBName and run it against some MSDB tables and DMVs. We 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

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_DBNamePRINT ‘– 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

Running the above procedure should produce the following output.

restoredb

Disclaimer

I was planning to do a blog post on https://www.sqlservercentral.com about this script 9+ years ago. I finally did it.