Backup Monitoring and Reporting

Database recovery is a core task of database administration and is defined as executing the tasks necessary to backup, save, retrieve and restore databases from hardware, software and user error. Your database recovery procedures should be documented and regularly tested by restoring either a full or random sample of backups. A key part of database backups is monitoring whether backups are in fact occurring. A common mistake in backup monitoring is only checking for backup success or failure when there are in fact three possible outcomes; success, failure and nothing. What is nothing? Nothing is what happens when a database or entire SQL instance for that matter is not configured for backups or the SQL Agent job or Windows services which are often a part of 3rd tools are in a hung state. In order to achieve effective backup monitoring you need to look at the non-event of a lack of backup rather than failed backup messages. Regardless of how a database is backed up, whether through SQL Server native backups or any one of a number of commercial backup software a row is written to the msdb.baclkupset table. Knowing this information, you can create daily monitoring and backup reports.