Database Dump Monitoring

About a month ago I came across a wonderful idea. I thought about how I could use Operations Manager to monitor my databases to ensure they have had a backup done within a predefined amount of time. I know lots of DBAs out there that have similar checks in place, most of which involve reports that may or may not be tied to jobs within SQL Agent. And while many of the solutions are adequate, I like solutions that offer pretty lights.

So, I dived into Operations Manager to see what I could see. I found the right target (database engine), and then put together some t-sql that would return the following three columns for each database: database name, days since full dump, days since differential. The code simply goes against the sysdatabases and msdb.backupset tables. I actually have two different versions of the monitor in place, one for sql 2000 and another for sql 2005. And I have plans to deploy to sql 2008, once we start rolling those boxes out.

The end result is that we have real time alerts that send an email notification as soon as a database is found to have a missing backup. I think that is better than waiting until the next day, but I am spoiled because we have a wonderful support team that works during our night shift. We also have the Database State view inside of Operations Manager that will give me a visual interpretation regarding the health of my enterprise.

I gave a demo of this monitor at PASS last week, and it seemed to be well received. I am looking to write up an article about this monitor as well, for those that enjoy looking at the gory details.