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 = ‘test@test.com’, –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
Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.
I am the author of the book “DP-300 Administering Relational Database on Microsoft Azure.” I can be reached at: Rajendra.gupta16@gmail.com for any consulting help.
- MSSQLTips Awards:
- Author of the Year – 2022 | Author Contender – 2021/2023/2024 | Champion Award (100+ tips) – 2020