Background

Recently, the business side of our company requested that our team develop a process to ensure that the database backups were current and still available on disk. This decision was spurred by a recent disaster in which we could not bring one of our database servers back from the dead. As (bad) luck would have it, the backup jobs, along with our alert notifications, stopped working about three weeks previously, making for a rather uncomfortable Monday morning. The business wanted assurance that such an event would never happen again. Our team agreed and sat down to work out the details.

Like many shops, we use an in-house developed system for monitoring our database servers. It is called DBA_Perform and it has been built over the past eighteen months. The solution that our team was about to incorporate would involve the use of this system. Code for all of the tables and stored procedures for the solution can be found in the downloadable zip file here.

A Simple Plan

We came up with a simple idea. First, find the backup file information for each database from the msdb system database. Then, find out if the latest full or differential backup for that database is less than twenty-four hours old. Lastly, make certain that the backup files still exist on disk, to ensure a quick recovery without having to go to the tape backups. Simple, right? The only stumbling block at this point was how to verify if a file was still on disk.

I began to create a stored procedure that would do exactly that. It would take a filename as an input parameter and run a command shell in order to use the dir command in DOS. Details of this can be found in the DBA_Verify_File_Exists procedure in the downloadable zip file. And while I was quite proud that I was able to do this rather easily, a colleague then pointed out to me the undocumented extended stored procedure, xp_fileexist. As I was about to rewrite everything to use the extended stored procedure, I remembered that Microsoft always tells us that undocumented objects are subject to change without notice. So perhaps it is best to use the DIR command, but feel free to make modifications if you desire.

Wheels in Motion

With the procedure in place to check if a file exists on disk, we were ready to write the other

procedures that would do the bulk of the work. First up was a stored procedure that would query the msdb database and retrieve the actual backup filename. It is named DBA_Recent_Backup_files, and is included the zip file. The procedure takes the name of a particular database as an input string, and based upon that database name it searches for any full and differential backup files by using the following code:

select @backup_set_full = max(bs.backup_set_id)
from msdb.dbo.backupset bs
where bs.database_name = @dbname
and bs.type = 'D'
and bs.server_name = @@servername
select @backup_set_diff = max(bs.backup_set_id)
from msdb.dbo.backupset bs
where bs.database_name = @dbname
and bs.type = 'I'
and bs.backup_set_id > @backup_set_full
and bs.server_name = @@servername

And from this, everything will fall into place. First, we find the backup_set_id for the most recent full backup. Then, using that backup_set_id, we find the most recent differential backup, if one is available. If there is no differential backup, then the @backup_set_diff variable is NULL, and we respond accordingly.

With no differential, then all we are concerned about is the time of latest full backup and if that file is still on disk. The following code will return the physical_device_name for the full backup, which is the filename that will be passed to the DBA_Verify_File_exists stored procedure. If the physical_device_name is NULL, then there is no full backup that is less than twenty-four hours old.

select @physical_device_name= '"'+convert(varchar(200),bmf.physical_device_name)+'"'
from msdb.dbo.backupset bs, msdb.dbo.backupmediafamily bmf
where bs.media_set_id = bmf.media_set_id
and ((bs.backup_set_id = @backup_set_full))   -- need full
and bs.server_name = @@servername
and bs.backup_finish_date > dateadd(hh, -24, getdate())     --we want to find a full backup within the last 24 hours
order by bs.backup_set_id    --in case more than one is available, make certain we check only the most recent

If a differential backup does exist, then we want to check to see if that differential backup is less than twenty-four

hours old. Here things get a little trickier. It is not enough that we have a differential file available on disk

that is less than twenty-four hours old. Why? Because the differential backup is useless unless you have the original

database backup file to start with! So, we need to check that the differential is recent and available, and also check

that the full backup is available on disk. So we insert into a temporary

table two rows, one for the full backup filename, and one for the differential. From there, we verify that each file

exists on disk. All the while, we are building our error message that will ultimately be inserted into the Backup_Verify

table within our DBA_Perform database.

Create the Job

The last thing we needed to do was to put together a job that would run once a day and generate an email notification if necessary. We created a job that simply calls a procedure named DBA_Recent_Backups (also included in the zip file). This procedure creates a cursor that steps through all of the databases (filtering out pubs, tempdb, and Northwind, of course) and passes each database name to the DBA_Recent_Backup_files stored procedure. The DBA_Recent_Backups procedure will then retrieve a count of the rows in the Backup_Verify table that are less than 24 hours old.

Included in the email notification is a select statement to assist the DBA currently on call in troubleshooting the situation. It was discovered that inclusion of the filenames that are being retrieved from the msdb would greatly reduce the amount of time necessary to troubleshoot, as the DBA would not need to step through the code or look through the msdb database. We could have included the filenames in the email, and feel free to format the notification as you desire. The filenames are stored in the backup_verify table, and there is a filename for the latest full backup as well as the latest differential.

Conclusions

Face it, DBAs are working best when they are not seen. If you have six managers in your cube on Monday morning, something has gone horribly wrong. We all have our own tool boxes that we have built up over time, and in the end they all get some of the basics accomplished. Backup the databases, scan the error logs, check for failed jobs each morning, etc. But management will always want to know what will be done to minimize risk in the event of a disaster The attached code is yet another tool that can assist a DBA to assure their company that the database servers are as stable as possible.

Download the code