Databases With No Recent Backups

I've written the script below to easily identify databases that have not had backups taken within a certain amount of time. This could be useful if you need to build your own monitoring. However, even if you use a third party monitoring tool,as I do, it is helpful to run this query on the server for which I've received an alert.

Many of the servers I manage have a large number of databases. The alerts I receive for overdue backups are database specific. But, in general, if one database is overdue, chances are something has happened on the server. So this query can give me a quick overview.

At the top of the query there are three (3) variables:

• DECLARE @LogAge INT --Hours
• DECLARE @DiffAge INT --Days
• DECLARE @FullAge INT --Days

Set these according to what you consider makes a database backup to be out of date. For example, if you take daily full backups you'll want to set @FullAge to -2 (or even -1). The query will then find any database whose latest backup was taken more than two days from the time of running the query. 

As always, I hope you find this helpful.