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 CaraxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.