I thought this was a pretty good reminder of what we are and are not backing up. I did have to do some modifications in my case - we do transaction log backups and database level backups. Our database backups are done both to tape and to file (two separate backups). If I want to know when the actual last database backup was, this needs to be modified a little bit as follows:

Select a.name as DBName, cBackupType, cBackupLocation, dBackup from master.dbo.sysdatabases a

left join

(select database_name as DBName,

CASE WHEN type = 'L' Then 'Log'

WHEN type = 'D' THEN 'Database'

ELSE 'Other'

END as cBackupType,

CASE WHEN user_name = 'DOMAIN\TapeBackupAccount'

THEN 'Tape'

ELSE 'Disk'

END as cBackupLocation,

max(backup_finish_date) as dBackup

from msdb.dbo.backupset where backup_finish_date <= getdate()

group by database_name, type, user_name) B

on a.name = b.DBName

When I select from this, I'm able to filter out Disk/Tape and Log/Database backups. I didn't include differential backups because we don't use them at my company. This was very useful to find those databases which may have been backed up to disk for a transaction log backup, but no recent db backup. It was also useful to find those db's that have only been captured on tape recently.

Overall, this was a great reminder and gave me a little nudge to check on my own systems. Thanks for the article.

-Pete Schott