15 April,2017 by Jack Vamvas
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