Script to check if a SQL database has a backup

Not being able to get data back from the backup system , is  one of the more embarrassing moments for a DBA.  Vigilance needs to be maintained  to ensure that what is expected to be backed up is actually backed up.

It doesn't matter what backup software you're using , you're still dealing with the same problem, can you recover the database with the expected RPO back onto the system in an acceptable RTO.

There is  some great backup software on the market which has all type of reporting and alerting,  checking if a database backup fails or corrupts. And then you have the problem of Schrodinger’s backup - A warning for DBAs  - which states the condition of a backup is not known until a restore

As a DBA there are two parts to the checking if a backup is in place. The first part is via the SQL Server. Is there an entry missing  in msdb for the database?  This script can check to see if there is a database in the sysdatabases catalog - but no entry in the msdb catalog. 

SELECT sdb.name [DB_NAME]
FROM sys.sysdatabases sdb
LEFT JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.backup_finish_date IS NULL

There are plenty of situations where an database backup exclusion can be in place , for whatever reason, only to find you don't have a backup. You cannot automatically assume because there is an entry in the msdb database that the backup file exists.

The second part of the problem  is checking if there is a valid backup in the backup system. For example, most backup system have a retention period facility. Rather than keeping a database backup for an extended period , a retention period may be set - such as 30 days , and then once the file reaches 30 days it drops off the system.   So some sort of Exception based reporting is required to check the retention periods are in place

 Read More

Backup history for a single SQL Server database (SQL Server DBA)

SQL Server BACKUP folder in windows registry