Database Backup Monitoring

Not everyone has access to off-the-shelf monitoring tools. One of my clients is a very small shop with only a few databases and 2 database servers. But that doesn't mean they don't need monitoring. Over the past few weeks I've been implementing various system alerts and now I've deployed the backup monitoring stored procedure below.

In summary it performs the following:

• Declares the age thresholds for the various backup types (Full, Diff, Log)
• Interrogates the msdb backup history tables
• Collects a list of files in the backup directories
• Sends an email in the case any thresholds are breached or files are missing

There are a few details that need to be kept in mind as well:

• Security policies need to allow xp_cmdshell to be enabled (this script checks if it is and if so enables it. If it was originally disabled, it will then be disabled)
• The SQL Agent account needs access to the backup directories. If it doesn't you may need to create a proxy
• It relies on database mail, so be sure to enable and configure it on your instance

And here's the stored procedure:

Acknowledgements: - Geoff on stackexchange for the xp_cmdshell script; Kalman Toth (sqlusa.com) for the file path parsing script.