Identifying SQL databases that slip through the cracks with MOM 2005

12 days until Christmas, if you count today and the 25th. MS Operations Manager 2007 RC2 is here, so it's time to change focus. But MOM 2005 is still here taking care of business, and cannot be ignored (you know what they say about a woman scorned). I'm going to try and drop something useful on the site for MOMv2 every day between now and December 25.....a "12 Days of Christmas" for MOM, so to speak.   So, On the 1st day of Christmas, my true love had SQL databases not being backed up in her environment.   MOM 2005 does a good job at alerting on database backup failures.....but what about databases that NEVER GET BACKED UP AT ALL?  Some environments simply lack the continuity from an operational process perspective to ensure that new databases deployed are given a proper maintenance plan that includes a periodic full backup. Some IT folks forget to mention the little things like, "Hey DBA's, I deployed this really important database on a new server today!".....maybe I am just splitting hairs :(   Here's a simple script (Last Full SQL Backup) that will alert you of every database on every server in your environment that hasnt received a full backup in a user-defined number of days.   Script Parameters:   MaxDays - integer defining max number of days a SQL database should  go without being backed up (a FULL backup) in your environment. I would suggest 5 days              as a suitable default if you are unsure. LogSuccessEvent - boolean 1-logs event for each successful run 0-disables success logging. WARNING - this will log 1 event for each SQL database on  every SQL server it runs against!   Installation: Configure this script as part of an event rule with a timed provider to run in your environment every 24 hours. Include in a rule group associated with all your SQL Servers (works on SQL 2000 & 2005 for sure). Pay attention to the script parameters described above (also documented in the script header).   Get it here: http://www.momresources.org/momscripts/lastfullSQLbackup.txt   Alerts: Generates a very simple alert to diplaying the database name and days since last full backup. Description: last full backup on MyDatabase has not occurred for 30 days. Name: SQL Last Database Backup Severity: Warning Resolution State: New Domain: ESC Computer: MYSERVER Time of First Event: 12/13/2006 9:25:01 AM Time of Last Event: 12/14/2006 9:25:02 AM Alert latency: 1 sec Problem State: Investigate Repeat Count: 7 Age:   Source: SQL Last Full Backup