Find out recovery model changes

Question

I'd like to work around instances where a database were changed to simple and then reverted back to full or bulk logged recovery models. This is for my backup maintenance plan, as obviously any following log/differential backups will fail. Are there any DMOs available which monitor recovery model changes?

Answer 1

You can see this information in the SQL Server Log. This link has a query text that shows an example of searching for that specific item.

https://www.sqlrx.com/find-out-who-changed-the-database-recovery-model/

For your convenience, I have included the script in the above link here. This is not my script, but the one from the site above, with some formatting fixes.

DECLARE @tracefile VARCHAR(500);
DECLARE @ProcessInfoSPID VARCHAR(20);

CREATE TABLE [dbo].[#SQLerrorlog]
    (
        [LogDate] DATETIME NULL
        , [ProcessInfo] VARCHAR(10) NULL
        , [Text] VARCHAR(MAX) NULL
    );

/*

Valid parameters for sp_readerrorlog

1 – Error log: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…

2 – Log file type: 1 or NULL = error log, 2 = SQL Agent log

3 – Search string 1

4 – Search string 2



Change parameters to meet your needs

*/

--Read error log looking for the words RECOVERY

--and either FULL, SIMPLE or BULK_LOGGED indicating a change from prior state
INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, 'RECOVERY', 'FULL';

INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, 'RECOVERY', 'SIMPLE';

INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, 'RECOVERY', 'BULK_LOGGED';

UPDATE #SQLerrorlog
SET ProcessInfo = SUBSTRING(ProcessInfo, 5, 20)
FROM #SQLerrorlog
WHERE ProcessInfo LIKE 'spid%';

-- Get path of default trace file
SELECT @tracefile = CAST(value AS VARCHAR(500))
FROM sys.fn_trace_getinfo(DEFAULT)
WHERE traceid = 1
    AND property = 2;

-- Get objects altered from the default trace
SELECT IDENTITY(INT, 1, 1) AS RowNumber
       , *
INTO #temp_trc
FROM sys.fn_trace_gettable(@tracefile, DEFAULT) g -- default = read all trace files
WHERE g.EventClass = 164;

SELECT t.DatabaseID
       , t.DatabaseName
       , t.NTUserName
       , t.NTDomainName
       , t.HostName
       , t.ApplicationName
       , t.LoginName
       , t.SPID
       , t.StartTime
       , l.Text
FROM #temp_trc t
    JOIN #SQLerrorlog l ON t.SPID = l.ProcessInfo
WHERE t.StartTime > GETDATE() - 1 -- filter by time within the last 24 hours
ORDER BY t.StartTime DESC;

DROP TABLE #temp_trc;
DROP TABLE #SQLerrorlog;
GO

Answer 2

One option is to use a SQL Server SERVER TRIGGER. Here is an example of one that interrogates EVENT data and sends and email to alert on recovery model changes.

USE [master]
GO
create TRIGGER [SCUTILITY_DDL_ALTER_DB] 
ON ALL SERVER 
FOR ALTER_DATABASE as

declare @text nvarchar(max)
declare @login varchar(128)
declare @recovery smallint
declare @body nvarchar(max)

SET @text = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 
SET @login = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]' ,'varchar(128)') 
SET @recovery = PATINDEX('%RECOVERY%',@text) 
SET @body = 'Login=' + @login + char(10) + 'Event=' + @text

IF @recovery>0
BEGIN 

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SqlServerEmailProfile',
    @recipients = '<YourEmailAddress>',
    @body = @body,
    @body_format = 'text',
    @subject = 'Alter database - Recovery model changed!',
    @importance = 'High';
END 


GO

ENABLE TRIGGER [SCUTILITY_DDL_ALTER_DB] ON ALL SERVER
GO