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.