Background
Untested backups. An awful lot is said about taking regular backups, although an inordinate number of people out there appear to ignore it. What is less talked about is actually checking that the backups are usable – it’s no good having a series of backups for that fateful day to discover that when they’re needed that they’re useless.
There are a handful of articles out there regarding checking backups. Some of them use PowerShell, which I have little experience in at this point and some are in software packages that either require payment or aren’t quite ready (at the time of writing this article).
Currently I use Ola Hallengren’s excellent backup scripts which can verify the backup. However, verify does not check the database to the degree that DBCC does and that was what I was after.
The Problem
I needed something that would automate the DBCC CHECKDB process against the latest backups that I have for any specified server. Of particular interest (of course) are the databases on the live server but I did not want the verification process to run on this server – it has more important things to do.
The Solution
Fortunately all of the servers that I’m interested in share a drive for the backups, so my development server can see the folder used for storing backups by the live server. I created linked servers on my development instance to point to whichever servers I was interested in. The logins associated with these linked servers don’t require any special permissions – the only purpose of these is to enable the process to access that backup history and details tables from the specified server.
I use a database on every server that I’m responsible for, to keep data of use to myself in the execution of my tasks. This may be monitoring information or temporary data that I just need to keep somewhere. For this purpose I have a database named ‘DBA’ on the development server and within that I create a basic table to store the results from the stored procedure (if required – it is optional).
Basic Description
The stored procedure extracts backup information from the targeted server. It then extracts information from the backup header so it can construct the sql code required for the restore with the correct files for the latest backup. The database is restored with ‘-DBCC’ added to the name and in standby mode, to reduce the chances of anybody accessing it for the brief time it exists. DBCC then executes against the database and stores the results in a temporary table. The database is then dropped and processing moves on to the next database. Once all of the databases have been checked this temporary table is checked and the pertinent information is selected (into a target table, if specified).
At the end of the processing there is a rollback file left in the default database location. This is generally a small file and I decided not to remove it, as this would require that xp_cmdshell is permitted and not all environments allow that.
Parameters
@LinkedServer AS NVARCHAR(100) – The linked server name which will have backups checked. If not supplied then the local server name will be used.
@BackupDate AS DATETIME – When extracting the backup information, no backup details from before this datetime will be selected.
@DBCC_INFOMSG AS BIT – If 0 then no information messages will be produced – there will only be messages if errors or inconsistencies are detected by DBCC.
@OutputToTable AS NVARCHAR(30) – The three-part name for the destination for any information messages from DBCC. If not supplied then the stored procedure executes a simple SELECT statement.
@Exclusions AS BackupVerificationExclusions – A table parameter that contains names of any databases to be excluded from this process. The code to create the table type is supplied at the start of the downloaded code.
Execution
I have a Job that executes this stored procedure each day and because I have sent the output to a table, I can check that table for any validation failures from DBCC. An example of the job code is supplied at the end of the download source.
The disclaimer
I need to stress that this solution works for my environment. It does exactly what I require of it and has been tested against the situations that I’m likely to encounter at this site. This solution may not be ideal for anybody else but I’m posting it to provide information and ideas to others – it may be of no use in itself but might help you come up with a solution that works for you. There are no warranties, implied or otherwise with this software and like any code that you extract from the internet – make sure you understand it, because you’ll be responsible for it should you decide to implement it.
I run it against SQL Server versions 2012 and 2014. Some testing has been performed against 2016 SP1.
Download
The source of the stored procedure can be downloaded from here.
Links
RESTORE VERIFYONLY
Ola Hallengren backup scripts
DBA Tools (Powershell)
Corrupt databases (for testing)
Notes on SQL 