Backup Sanity Check

Leave a comment

August 26, 2010 by Mike Hillwig

One of my pet peeves is when an application thinks its smarter than its DBA. Our helpdesk system was occasionally complaining that the database hadn’t been backed up in the past 24 hours. And our helpdesk manager was understandably concerned. That’s when I started doing some digging.

My backup script was occasionally skipping databases that had a SIMPLE recovery model. I’m still trying to figure that one out. More importantly, I set the recovery model to FULL, as it should be. * The latest upgrade of the vendor’s software set it to SIMPLE. But still, I was scratching my brain, trying to figure out what was wrong with my script.

In the meantime, I wanted to update my backup script to add a sanity check, backing up anything that hadn’t been backed up in the past 24 hours. I headed for the master.sys.databases table to see if I could find the column that indicated when it was last backed up. That’s the logical place for it, right? Wrong.  After doing some spelunking, I drew up this little gem:

SELECT      sd.name, max(bs.backup_finish_date)
FROM        master.sys.databases sd
LEFT JOIN   msdb.dbo.backupset bs
ON          bs.database_name = sd.name
where       ISNULL(type,'D') = 'D'
GROUP BY    sd.name
HAVING      max(backup_finish_date) IS NULL
OR          datediff(hour,max(backup_finish_date), getdate()) > 24
order by    sd.name

The backupset table gave me a great place to start.  But I found a little problem–it was listing a bunch of databases with really old backup dates. That’s when I realized those databases had been dropped long ago. By joining it to the master database, it only showed databases that were currently attached. And as an added bonus, a couple of NULL checks allowed me to see anything that had never been backed up.

I shouldn’t have to use this, but it is nice to know that I can use it as a sanity check.

* In my environment, the only databases that get SIMPLE recovery is static data. Everything else gets set to FULL.