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.