• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 627
  • Last Modified:

SQL script to monitor recovery model changes and last time a backup was taken.


I need a script that will alert me via email if one of the following events occur:
1. A new database is created.
2. Recovery model not set to FULL.
3. There is no full backup for a database.

I would like this script to be dynamic if possible, and by that I mean that an email is sent the moment the recovery model is changed - is this possible or will I have to schedule it to run on a regular schedule?

Our SQL estate is a mixture of 2008R2 and 2005 but there is still one pesky box on 2000.

Many thanks
Jay Carax
Jay Carax
1 Solution
David ToddSenior DBACommented:

I think the script will have to be scheduled.

Some of what you want needs a time delay - ie note that the database was created, but wait 24 hours before alerting to no full backup.

Many businesses set model to simple recovery model. Also tempdb is simple by default (and can't be backed up) So some of what you are asking needs to be filtered.

Check out Brent Ozar's sp_blitz procedure, which checks for most of what you've asked, along with a garzillion other things that could hurt.


So, you can pull apart Brent's script and use that as the basis for your own, or get Brent's script to dump into a table each day, and another script to note day-on-day changes and report.

Do note that this script does not run on SQL 2000, which has gone end of life. I still have a few servers on SQL 2000, and have a cut-down version of sp_Blitz that I run each day and note the number of lines. If that changes, then I investigate.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now