Query who modified the database recovery mode

Some people in the QQ Group said that their database recovery mode was automatically modified, but no JOB was modified. They asked me if I could find out what happened.

In fact, the SQL Server Error Log records the information about the database recovery mode changed. For example, I run the following script:

 

Alterdatabase sql2008 setrecovery simple

Go

Alterdatabase sql2008 setrecovery full

Use sp_readerrorlog to view the following information:

2013-09-1309: 57: 45.200 spid51 Settingdatabase option RECOVERY to SIMPLEfor database sql2008.

2013-09-1309: 57: 48.980 spid51 Settingdatabase option RECOVERY to FULLfor database sql2008.

We can see that the Error Log gets less information and cannot know exactly who was executing the command code and what the application was. However, the change of the object will be recorded in the Default Trace, the event class is 164, and the change of the database recovery mode will also be recorded in this event. If you combine the Default Trace and Error Log information, you can obtain more detailed information to find the script, user name, and application name that was running at that time.

There is a problem here. By default, sp_readerrorlog only obtains the current error log, but the error message may not be in this log. Therefore, the following script uses the Stored Procedure sp_enumerrorlogs to cycle all log files.

The script is as follows:

 

-- Query all Error Log files and find the information for modifying the Recovery Mode. Because the Error Log may be Recycle,

-- So we use the Undocomented Stored Procedure master .. sp_enumerrorlogs to loop all Error Log Files

Setnocounton

 

Declare @ searchString1 varchar (255)

 

Declare @ searchString2 varchar (255)

 

Set @ searchString1 = 'recovery'

 

Set @ searchString2 = 'option'

 

Declare @ logs table (LogNo int, StartDate Datetime, FileSize int)

 

Declare @ results table (LogFileNo int, LogDate Datetime, ProcessInfovarchar (20), Textvarchar (max ))

 

Insertinto @ logs EXECmaster .. sp_enumerrorlogs

Declare cLogs cursorforselect LogNo from @ logs

 

Declare @ LogNo int

 

Open cLogs

 

Fetch cLogs into @ LogNo

 

While @ fetch_status = 0

 

Begin

 

Insertinto @ results (LogDate, ProcessInfo, Text)

 

EXECsp_readerrorlog @ LogNo, 1, @ searchString1, @ searchString2

 

Update @ resultsset LogFileNo = @ LogNowhere LogFileNo isnull

 

FetchcLogsinto @ LogNo

 

End

 

Deallocate cLogs

 

 

Select * from @ results

 

 

--- Loop all DefaultTrace files

Declare @ logFile varchar (max)

 

Set @ logFile = (selectpathfromsys. traceswhere is_default = 1)

 

Set @ logFile = left (@ logFile, len (@ LogFile)-charindex ('_', reverse (@ LogFile) + '. trc'

 

-- Associate the log file with the Trace file to obtain the code, LoginID, and HOSTNAME for modifying the Recovery Mode at that time.

Select starttime ,*

 

Fromfn_trace_gettable (@ logFile, null) t

 

Join @ results r on t. StartTime betweendateadd (MS,-150, r. logDate) anddateadd (MS, 150, r. logdate)

 

Andt. spid = substring (r. ProcessInfo, 5, 10) -- required to enable ahash join to be used

Where t. EventClass = 164

 

And EventsubClass = 1

Original script from: http://sqlblogcasts.com/blogs/simons/archive/2011/04/27/how-to-find-out-when-a-db-recovery-model-was-changed.aspx

 

 

 

 

This article is from the "follow SQL Server Technology" blog, please be sure to keep this source http://lzf328.blog.51cto.com/1196996/1296231