Database recovery model change notification report for SQL Server
By: Rajendra Gupta | Comments (10) | Related: 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 @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
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.
View all my tips