By: Rajendra Gupta | Comments (10) | Related: 1 | 2 | 3 | > 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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
