Checking Backups

Over the past two years I have acquired quite a few database servers that I am responsible for.  We have many standards among the database group I am a part of, things such as standard drive configurations (drive for data files, log files, tempdb, and backup),  lock down scripts, maintenance jobs, etc.  Over time I have come to realize that previous dba’s could have and have changed some of these standards for any number of reasons.  For example, if a particular database on the server needed a different backup strategy they might exclude it from the standard script that backups up all user databases, master, and msdb.  Same goes for the scripts to rebuild indexes, etc.  If our standard monitors are only checking to the standard backup job or standard index rebuild job then how am I to know if all databases backed up properly without visiting each server to validate this?  Also what something happened and the dba on call needed to temporarily disable my full backup job and forgot to enable it back?  If our monitor is only checking for when it fails I will never be notified that it is not running.  This lead me to create a very simple process to query MSDB to get the max date/time the database backed up and to join the Master database to get the real database name.  I insert this result set into a centralized database and can now run a report each day to see when I have a database that hasn’t backed up within the past 24 hours.  Actually I have this automated to where I get a daily email.  I know there are tons of third party tools out there to monitor servers but when your company has zero dollars to spend right now on centralized management and or you don’t have the time to pursue researching those tools, spending a few hours on a Saturday afternoon to develop this process is a good trade off.  While researching this I also noticed that we did not have a process in place to remove historical backup information out of MSDB.  Now on a very large SQL 2000 consolidation server that has been running for many years with over 100 databases doing log shipping, MSDB can grow pretty large.  I had to create a few indexes in MSDB so that the stored procedure SP_DELETE_BACKUPHISTORY would run efficiently.  The indexes were media_set_id on backupset, restore_history_id on restorefile and restorefilegroup.  On a dozen servers I was able to reduce the size of MSDB and be able to start monitoring 215 production databases to ensure they are properly backed up each day.  I am sure over time I will find a few things to tweak with my process but for now it is working and helping me to make sure my databases are backed up.

This entry was posted in Backups/Recovery. Bookmark the permalink.

2 Responses to Checking Backups

  1. Oscar Zamora says:

    Hey Tim, we need to start maintenaining our MSDB databases as it is growing pretty large on some servers. A simple delete of old data works? If not, any links you can share?


  2. Guy Caruthers says:


    Working where I do, I completely understand your comment about dollars for centeralized management, so I would be very interested in code snipets you care to share. In the past I’ve captured what I can from logs and store in SQL Server to query and aggregate and your mention of using this to mine data from MSDB is something I can definately look into.

    One little project I worked on pulled the IIS logs from SharePoint WFE’s to aggregate in a datamart in order to show usage (hits, sites, pages, etc.) across our supposedly load balanced web servers. Makes tracing SharePoint performance issues a little easier when you can verify one of your WFEs didn’t serve any pages for a few hours. Again, tools out there if you have the $$, but Log Parser was a free download.

Leave a Reply

Your email address will not be published. Required fields are marked *