Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Database recovery model change notification report for SQL Server


By:   |   Last Updated: 2011-10-21   |   Comments (10)   |   Related Tips: 1 | 2 | 3 | More > Recovery Models

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 @[email protected]@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


Last Updated: 2011-10-21


next webcast button


next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Hi, 

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 03, 2011 - 4:58:13 PM - Dave Back To Top

Bastian,

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 02, 2011 - 2:08:22 PM - Bastian Black Back To Top

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 02, 2011 - 2:05:07 PM - Bastian Black Back To Top

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 02, 2011 - 12:41:51 PM - Dave Back To Top

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 02, 2011 - 9:02:01 AM - Bastian Black Back To Top

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

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

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

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

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

Is that what you are saying?


Learn more about SQL Server tools