查询谁修改了数据库的恢复模式

其实在SQL Server Error Log里面会记录数据库恢复模式被更改的信息。比如我运行下面的脚本：

alter database sql2008 set recovery simple
go
alter database sql2008 set recovery full

然后用sp_readerrorlog可以看到下面的信息：

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.

可以看到Error Log获得的信息比较少无法确切知道当时是谁执行的，命令代码，应用程序是什么。但是在Default Trace中会记录对象的变更，事件类为 164，修改数据库的恢复模式也会被记录到这个事件。如果将Default Trace和Error Log的信息结合起来，就可以获得更为详细的资料，从而找到当时运行的脚本，用户名，应用程序名称。

这里面有一个问题，默认情况下sp_readerrorlog只获得当前的错误日志，但是可能错误信息不在这个日志里面。所以下面的脚本使用存储过程sp_enumerrorlogs循环所有的日志文件。

脚本如下：

--查询所有的错误日志文件找到修改Recovery Mode的信息，由于Error Log肯能被Recycle，
--所以我们用Undocomented存储过程master..sp_enumerrorlogs循环所有的错误日志文件

set nocount on

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),Text varchar(max))

insert into @logs EXEC master..sp_enumerrorlogs

declare cLogs cursor for select 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


---循环所有的DefaultTrace文件
declare @logFile varchar(max)

set @logFile =(select path from sys.traces where is_default=1)

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

--将日志文件和Trace文件关联，这样就可以获得当时修改Recovery Mode的代码，LoginID，HOSTNAME 等等。
select starttime,*

from fn_trace_gettable(@logFile,null) t

join @results r on t.StartTime between dateadd(ms,-150,r.logDate) and dateadd(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