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

欢迎进入C/C++编程社区论坛，与300万技术人员互动交流 >>进入 在QQ群里面有人说自己的数据库恢复模式自动被修改了，但是没有JOB也没有人修改，问我是否查出到底发生了什么。 其实在SQL Server Error Log里面会记录数据库恢复模式被更改的信息。比如我运行下

欢迎进入C/C++编程社区论坛，与300万技术人员互动交流 >>进入

　　在QQ群里面有人说自己的数据库恢复模式自动被修改了，但是没有JOB也没有人修改，问我是否查出到底发生了什么。

　　其实在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存储过程mastersp_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 mastersp_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