查詢誰修改了資料庫的復原模式

在QQ群裡面有人說自己的資料庫復原模式自動被修改了，但是沒有JOB也沒有人修改，問我是否查出到底發生了什麼。

其實在SQL Server Error Log裡面會記錄資料庫復原模式被更改的資訊。比如我運行下面的指令碼：

 

alterdatabase sql2008 setrecovery simple

go

alterdatabase sql2008 setrecovery 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迴圈所有的錯誤記錄檔檔案

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 

 

 

---迴圈所有的DefaultTrace檔案

declare @logFile  varchar(max)

 

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

 

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

 

--將記錄檔和Trace檔案關聯，這樣就可以獲得當時修改Recovery Mode的代碼，LoginID，HOSTNAME 等等。

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 

原始指令碼來自於：http://sqlblogcasts.com/blogs/simons/archive/2011/04/27/how-to-find-out-when-a-db-recovery-model-was-changed.aspx

 

 

 

 

本文出自 “關注SQL Server技術” 部落格，請務必保留此出處http://lzf328.blog.51cto.com/1196996/1296231