Database recovery model change notification report for SQL Server

Problem

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.

Solution

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)

CREATE DATABASE [DBA] ON PRIMARY 
(
NAME = N'DBA', 
FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBA.mdf',
SIZE = 503808KB, 
MAXSIZE = UNLIMITED, 
FILEGROWTH = 1024KB )
LOG ON 
( 
NAME = N'DBA_log', 
FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBA_log.ldf',
SIZE = 20160KB, 
MAXSIZE = 2048GB , 
FILEGROWTH = 10%)
GO

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

USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DBA_DB_Baseline_Recovery_Model](
[DBName] [nchar](50) NOT NULL,
[RecoveryModel] [nchar](50) NOT NULL
) ON [PRIMARY]

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.

USE [DBA]
GO
CREATE PROCEDURE [sp_DBA_recoverymodel] 
AS 
BEGIN 
 SET NOCOUNT ON 

 
 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) 

 
 SELECT @servername=@@SERVERNAME 

 
 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 

 
 SET NOCOUNT OFF 

 
END 
GO

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'
ALTER DATABASE DBA SET RECOVERY SIMPLE
GO
EXEC sp_DBA_recoverymodel
GO

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

About the author

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

Article Last Updated: 2011-10-21

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

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 - 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.