[SQL]透過 Transaction Log(fn_dblog) 找回被刪除的資料

有時不小心把資料給刪掉了,或是不知是誰在什麼時間點把資料給刪掉,這時可以透過這個SP把被刪除的資料給找回來!

最近跟 James 討論為何「ApexSQL Log」這個工具可以讀到被刪除的資料呢?

原來它是透過 Transaction Log 來讀取資料的!

於是透過 Transaction Log 到網路上有找到「SQL Server – How to find Who Deleted What records at What Time」直接透過 Transaction Log 來把被刪除的資料給找回來!

蠻神奇的,只是Run在我的區分大小寫的DB中會發生錯誤,因為有些欄位名稱大小寫沒有一致,於是就調整了一下!

剛開始在SQL 2012中運作都正常,到了SQL 2005及SQL 2008,刪除的人員及時間居然沒有顯示出來。

再檢查一下,原來是因為sysusers跟fn_dblog中的Transaction SID Join不到,所以改用SUSER_SNAME也蠻清楚的!

另外,原本要輸入資料庫名稱,改為自動取得現在執行的資料庫。

修改後的SP,請從附件download,希望對大家有幫助,謝謝!

 

測試範例如下,


--先建立測試的資料,並刪除它
Create Table tbl_Sample 
([ID] int identity(1,1) ,
[Name] varchar(50),
[T1] int)
GO
Insert into tbl_Sample values ('Letter A', 11)
Insert into tbl_Sample values ('Letter B', 22)
Insert into tbl_Sample values ('Letter C', 33)
 
Select * from tbl_Sample
DELETE FROM tbl_Sample

--執行找回被刪除資料的SP
--1.輸入刪除的Table Name 從 Transaction Log 找所有被刪除的資料
EXEC Recover_Deleted_Data_With_UID_Date_Time_Proc  'dbo.tbl_Sample'
--2.輸入刪除的Table Name 從 Transaction Log 在時間區間中,找出所有被刪除的資料
EXEC Recover_Deleted_Data_With_UID_Date_Time_Proc  'dbo.tbl_Sample' ,'2013/05/23','2013/05/23'

 

執行SP後,會把被刪除的資料給找出來,如下圖,

image

 

PS.
1.它是從 Transaction Log 取出資料來分析的! 所以如果將 Transaction Log 清掉,就找不到資料了哦!
2.所以這個SP可以在您未清 Transaction Log 前,找出某個Table被刪除的資料。

如果有需要加強的地方,也請跟我說一下哦! 謝謝大家!

Support SQL 版本為 SQL 2005 以上 (SQL 2005 ~ …)

 

參考資料

SQL Server – How to find Who Deleted What records at What Time

 

SQL程式碼

找回被刪除的資料Script

ScriptV12.zip ( V1.2 2013/05/23 加入處理透過SSMS Edit Table 去刪除資料而找不到Delete Log問題) 

FindDelV1_3.zip (2014/05/15 Recover_Deleted_Data_With_UID_Date_Time_Proc V1.3  修改Server的定序跟Database不同,會有 Cannot resolve the collation conflict between 的錯誤)

Release Note:

-- 1.修改不傳入DB Name改取自DB_NAME()
-- 2.修改 sysname DataType to nvarchar(128)
-- 3.使用SUSER_SNAME 代替 sysusers join 找出操作人員
-- 4.修正在區分大小寫的DB中,會發生的錯誤
-- 5.加入判斷,如下是透過SSMS的Edit Row時,刪除的[Transaction Name] 會用 user_transaction [Transaction Name] IN ('DELETE', 'user_transaction')
-- ************************************************************************
-- v 1.3. Release Note
-- 1.修改Server的定序跟Database不同,會有 Cannot resolve the collation conflict between 的錯誤
-- Example: EXEC Recover_Deleted_Data_With_UID_Date_Time_Proc  'dbo.tbl_Sample'