verifiedIntroduction

A popular joke about DBAs and database backups (there are two types of DBAs: 1) DBAs that do backups 2) DBAs that will do backups) 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 while 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 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 latest database backup), to only discover that all this time I was backing up a database with a corrupted table.

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 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

The following solution will find the latest full database backup (based on a database name), restore it, check for data integrity, drop it, and generate a report with results, all programmatically within one 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; ENDPRINT ‘– 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 @SQLStringPRINT ‘—-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

Disclaimer

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