Database recovery model change notification report for SQL Server

By:   |   Comments (10)   |   Related: 1 | 2 | 3 | > Recovery Models


The database recovery model plays a crucial role for the recovery of a database.  With several DBAs having access to a SQL Server instance there are bound to be changes that are not communicated.  In this tip we cover a monitoring solution we deployed at our company to alert the DBAs if a database recovery model is different than what it is expected.


The following is the process to setup the alerting mechanism. 

We created a separate database named "DBA" to track the recovery model that each database should have as well as deployed a stored procedure for the alerting.

Step 1 - Execute the below script to create sample database DBA (or use one of your existing databases)

FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBA.mdf',
SIZE = 503808KB, 
NAME = N'DBA_log', 
FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBA_log.ldf',
SIZE = 20160KB, 
MAXSIZE = 2048GB , 

Step 2 - Create a table to hold the expected Recovery Models for all databases

CREATE TABLE [dbo].[DBA_DB_Baseline_Recovery_Model](
[DBName] [nchar](50) NOT NULL,
[RecoveryModel] [nchar](50) NOT NULL

Step 3 - Store the baseline Recovery Models for all databases (this assumes the current values are the expected values)

INSERT into [DBA_DB_Baseline_Recovery_Model] 
SELECT name, recovery_model_desc FROM sys.databases 

Step 4 - Create the Stored Prodcedure to report Recovery Model changes

This procedure also uses sp_send_dbmail to send out an email notification. These settings will need to be configured accordingly.

CREATE PROCEDURE [sp_DBA_recoverymodel] 

CREATE TABLE #current_model_info ( dbname1 NVARCHAR(50), model NVARCHAR(50) )
INSERT INTO #current_model_Info SELECT name,recovery_model_desc FROM sys.databases
DECLARE @tableHTML NVARCHAR(MAX) ; DECLARE @Recmodel VARCHAR(100),@dbname VARCHAR(100), @currentrecmodel VARCHAR(4000),@servername VARCHAR(4000)
SET @tableHTML = N'<html><body><h1><font size="5" color="blue">Recovery model change report</h1>' + N'<table border="1.5" width="40%">' + N'<tr><b><td>Server Name</td><td>Database Name</td><td>' + N'Expected recovery model</td><td>Changed recovery model</td></tr>' + CAST(( SELECT td = @servername,'', td = a.dbname, '', td = a.recoverymodel, '', td = b.model FROM DBA_DB_Baseline_Recovery_model a JOIN #current_model_info b ON a.dbname=b.dbname1 WHERE a.recoverymodel <> b.model FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table><BR><BR></body></html>'
EXEC msdb.dbo.sp_send_dbmail @recipients = '[email protected]', --Multiple emails can be added @subject = 'Recovery model change report', @body = @tableHTML, @body_format = 'HTML', @profile_name = 'profile name'; --Enter the DatabaseMail profile name

Now let's generate the Alert.  We will modify the DBA database that we just created.

SELECT name, recovery_model_desc from sys.databases where name='DBA'
EXEC sp_DBA_recoverymodel

Here is the output for the report

sql server recovery model change report

Next Steps
  • Create a SQL Server Agent Job to run on a set schedule to alert you of changes
  • Modify this to only send out alerts when there are changes
  • Modify the base table to only include databases that you want to be notified about if there are changes

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, December 29, 2017 - 10:39:26 AM - Monitor sql Back To Top (74572)


Is it only possible to have the script notifying us by mail  if the recovery model status change.

As a job will execute the procedure sp_DBA_recoverymodel, in the actual status even if no modification had been applied a mail will be sent.

Thank you


Thursday, November 3, 2011 - 4:58:13 PM - Dave Back To Top (15012)


Our clients still have DBAs that run the SQL boxes but rely on us to handle day-to-day tasks that can get in the way of getting other work done (Backups being a perfect example).  Since they no longer do this themselves, they make changes without always considering the implications from a backup perspective.

Wednesday, November 2, 2011 - 2:08:22 PM - Bastian Black Back To Top (14996)

ps. Don't get me wrong, I like the script and it's certainly handy to have such scripts around. I would like it more in my case to keep watch of any new SQL servers that pop up in our network, which sometimes happens when depts hire external companies to set something up for them... yet they expect me to be 100% responsible for the data.. :D

Wednesday, November 2, 2011 - 2:05:07 PM - Bastian Black Back To Top (14995)

I work at a DataCenter where many of our SQL Servers are managed but still fully accessible by our clients.

What do you define under 'fully accessible'?
Do they run on their own hosts? Are they running on their own instances, seperated from the other boxes? Or do they have full access to their SQL boxes, and could for example create a linked server to other client's boxes?

Going from full to simple means changing your log backup schedule and it means they could break the current tail logs. I doubt very much where you would want to be in a situation where your clients dictate what backup plan you have for this data in such a way that they can arbitrarily change it at will. In that case, what constitutes being a DBA for such a SQL server? For me, that translates to being responsible for the data, guaranteeing that it's consistent and safe in the form of a backup that can be restored in the manner they wanted (either point in time restore or not, depending on their needs).

From my perspective, where I am a DBA for a corporate, it's very simple. Only those instructed in the strictest protocols and knowledge gain access to such settings, as these settings should never arbitrarily be changed.

Unless, in your case they are all on their own little islands, but in that case, why bother?

Wednesday, November 2, 2011 - 12:41:51 PM - Dave Back To Top (14993)

I disagree Bastian.  I work at a DataCenter where many of our SQL Servers are managed but still fully accessible by our clients.  They can make any changes they need for their business and we need to ensure that we handle those changes accordingly.  Determining if they changed a recovery model from Simple to Full for example is crucial so that we can move the database to a policy in our backup software to backup tlogs.

Wednesday, November 2, 2011 - 9:02:01 AM - Bastian Black Back To Top (14990)

I think if you need this script then you have far larger issues than just the fact whether a recovery model changed or not.

I would certainly fix this either through policies, or by simply making sure only those that know what they are doing will have access to these settings.



Friday, October 28, 2011 - 2:52:44 PM - sreekanth bandarla Back To Top (14957)

If you are on SQL 2008 and above, i personally recommend using PBM to acheive the same with less pain(We are using it and it's flawless)

Wednesday, October 26, 2011 - 3:19:25 PM - Paul Miller Back To Top (14931)

OK - I will give that a shot.

Thanks for the quick reply and all of the tips.

Ya'll are great!

Wednesday, October 26, 2011 - 2:49:12 PM - Greg Robidoux Back To Top (14930)

Yes you can either create a stored procedure and have the job call the stored procedure every hour or you can just include the code in the job and not create a stored procedure.

Wednesday, October 26, 2011 - 8:50:21 AM - Paul Miller Back To Top (14927)

Can you just add this SP to a job that runs every hour to notify you?

Is that what you are saying?

get free sql tips
agree to terms