[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
從上面這個可以看出目前這台 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
這樣我們就可以取得物件被異動和資料庫被刪除的相關資訊了。至於上面 SQL 中有一些物件編號對應的部分，如果需要詳細的相關資訊，可以參考 MSDN 上面「ObjectType Trace Event Column」上有更詳細的所有說明。從上述的結果中可以看到藍色的部分，就會有紀錄到資料表被刪除的紀錄，紅色的部分紀錄資料庫被刪除的資訊，因此不用擔心要是被刪除的話，無法從那些物件編號取得對應的名稱，在這裡面都有記錄當時被異動前的物件名稱。
但如果大家覺得這樣太過於麻煩，要去記這些 DMV 實在有點煩人，不想去記這些指令的話，其實在 SQL Server 裡面，透過 SSMS 所提供的管理報表─「結構描述變更紀錄」，也可以有類似的功用。您可以按照你的狀況來決定要自己下指令還是用報表，反正不管黑貓白貓，會抓老鼠的就是好貓囉。
希望大家都不要遇到這種資料庫或者是資料表被刪除的狀況，才要辛苦的回來找證據的麻煩問題，畢竟被刪除之後，透過這些方式還是沒有辦法把資料救回來，只能亡羊補牢，想辦法找到兇手釐清責任而已。