0

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?

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.