Daily DBA: Check Database Backups

From time to time I post some of the things I do to check my systems each day. Sometimes I use PowerShell, sometimes the Management Data Warehouse, and sometimes I use a Policy from Policy Based Management (PBM). For the backup checks, I use a combination – I use a PowerShell command to evaluate a Policy against a set of servers. It checks to ensure every database is backed up daily.

The Policy itself is "borrowed" from the ones that ship with SQL Server 2008. You can find those here on x64:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033

And here for 32-bit:

C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033

And the name of the Policy I’m running is: Last Successful Backup Date.xml

Even though it’s not "mine", here’s the way that Policy is set up:

Targets: Every Database

Evaluation Mode: On Demand

Server Restriction: None

Check Condition:

Facet: Database Maintenance

Expression: @LastBackupDate >= DateAdd(‘day’, -1, GetDate())