[SQL][問題處理]是誰把資料庫給刪除了 !!!

[SQL][問題處理]是誰把資料庫給刪除了 !!!

早上進到公司來,原本想先悠閒一下的整理資料,把最近聽一些課程的筆記和文件彙整一下,預備一下 Techday 還有三十分鐘的短講。但才正在想的時候,就收到一個同仁氣急敗壞地打電話來,想要詢問是否有辦法幫忙解決他所遇到的問題。

 

事情是這樣的,幾天前他幫忙安裝一台 SQL Server,當時確認是有安裝好 SQL Server,並且把資料庫給建立好了,但今天一早來卻發現不見了。正常來說資料庫不應該平白無故的不見,因此他們想透過 Windows Event Log 或者是 SQL Server Error Log 來找資料,但都只有建立資料庫的資訊,並沒有任何刪除的紀錄,而通常在這個時候,決定沒有人會跳出來自首說是他做的,因此就只能發揮柯南的精神,想辦法來找出兇手囉。而他們上網路上找相關資料的時候,剛好看到我之前所整理的另外一篇「不要偷改我的資料庫啦 !」,利用 DDL Trigger 來做紀錄,但那個是要在事前發生前就要先設定好,對於已經發生過的事實就沒有辦法來做追蹤了,因此就電話來詢問是否有地方可以查詢相關異動的紀錄。

 

一開始我原本先想到的也是檢查相關的 Log 紀錄,但這些上面如同同事所描述的,的確是只有建立的資訊,而沒有任何刪除的紀錄。因此就想到說,是否可以利用系統的 Default Trace 來查看。當 SQL Server 安裝之後,正常狀況下如果沒有特別指定要關閉的話,那麼 SQL Server 會啟動一組預設的 SQL Trace,我們可以透過「sys.traces」這個系統檢視來查看這個預設追蹤,一般來說這一組會是 id 編號為 1,並且 is_default 欄位會設定為 1。

SELECT * FROM sys.traces

image

 

從上面這個可以看出目前這台 SQL Server 在系統目前的預設 Trace 是寫入到 「C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_78.trc」內,預設最多保留五個,因此可以推論知道應該會有 log_74.trc ~ log_78.trc 這五個檔案。既然要取出這些紀錄檔的內容,在這裡我們可以使用「sys.fn_trace_gettable」來取得資料,利用類似以下的語法來處理,在這裡因為我們不確定是在甚麼時間點把資料庫給刪除的,因此我們先設定從 log_74.trc 這個檔案開始來找,搜尋還存在的這五個追蹤檔。

select * from 
  fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_74.trc',5)

 

但這樣出來的資訊有點多,也比較複雜一點,因此我稍微調整一下,只取出部分我們所要注意的欄位,像是資料庫名稱、物件名稱、應用程式、電腦名稱和時間,其他我就先暫時不去管他了。

WITH ObjectTypeMap(Value,ObjectType) AS
(
    SELECT * FROM 
        ( Values ( 8259, 'Check Constraint' ),(8272,'Stored Procedure'),(8277, 'Table'),(8278,'View'),
                 (16964, 'Database'),(17235,'Schema' )
        ) as TypeMap(Value,ObjectType)
)
SELECT e.name,f.DatabaseName,m.ObjectType, f.ObjectName,f.ApplicationName, f.HostName,f.NTUserName,f.StartTime 
  FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_74.trc',5) f
  JOIN sys.trace_events e ON f.EventClass = e.trace_event_id AND f.EventClass in (46,47,164) and f.EventSubClass = 0
  JOIN ObjectTypeMap m ON f.ObjectType = m.Value
  WHERE DatabaseName = 'DEMO'  
  ORDER BY f.StartTime DESC,f.EventSequence DESC

image

 

這樣我們就可以取得物件被異動和資料庫被刪除的相關資訊了。至於上面 SQL 中有一些物件編號對應的部分,如果需要詳細的相關資訊,可以參考 MSDN 上面「ObjectType Trace Event Column」上有更詳細的所有說明。從上述的結果中可以看到藍色的部分,就會有紀錄到資料表被刪除的紀錄,紅色的部分紀錄資料庫被刪除的資訊,因此不用擔心要是被刪除的話,無法從那些物件編號取得對應的名稱,在這裡面都有記錄當時被異動前的物件名稱。

 

但如果大家覺得這樣太過於麻煩,要去記這些 DMV 實在有點煩人,不想去記這些指令的話,其實在 SQL Server 裡面,透過 SSMS 所提供的管理報表─「結構描述變更紀錄」,也可以有類似的功用。您可以按照你的狀況來決定要自己下指令還是用報表,反正不管黑貓白貓,會抓老鼠的就是好貓囉。

image

image

 

希望大家都不要遇到這種資料庫或者是資料表被刪除的狀況,才要辛苦的回來找證據的麻煩問題,畢竟被刪除之後,透過這些方式還是沒有辦法把資料救回來,只能亡羊補牢,想辦法找到兇手釐清責任而已。