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

前言

之前寫了一篇 [SQL]透過 Transaction Log(fn_dblog) 找回被刪除的資料 ，
有調整 SQL Server – How to find Who Deleted What records at What Time 它的 Stored Procedure ，但是因為直接執行會有一些問題。
而原本修正的附件又不見了，有許多人在詢問，所以就再寫一版到 Github上。

修正說明

從 binary/varbinary 字串轉換成 datetime 時，轉換失敗

如果直接執行時，會發生「從 binary/varbinary 字串轉換成 datetime 時，轉換失敗。」的錯誤。
會發生這個問題是因為將資料從 Varbinary Reverse 回來時，在有些定序會轉換錯誤，例如
Chinese_Taiwan_Stroke_CI_AS 定序會發生錯誤: 0x0000000041AC0000 => 0x00AC004100000000

select DATABASEPROPERTYEX(DB_NAME(),'collation') as collation
, CAST(0x0000000041AC0000 as varbinary) as org_bin
, CAST(REVERSE(0x0000000041AC0000) as varbinary) as reverse_bin

SQL_Latin1_General_CP1_CI_AS 定序會是正確: 0x0000000041AC0000 => 0x0000AC4100000000

select DATABASEPROPERTYEX(DB_NAME(),'collation') as collation
, CAST(0x0000000041AC0000 as varbinary) as org_bin
, CAST(REVERSE(0x0000000041AC0000) as varbinary) as reverse_bin

所以原本值應該是要 0x0000AC4100000000 ，結果卻被轉成了 0x00AC004100000000 ，再轉成 DateTime 自然就爆了。

所以，建立一個 自定的Reverse 的 Function 來給 Stored Procedure 使用，

IF OBJECT_ID (N'dbo.ufnReverse', N'FN') IS NOT NULL  
    DROP FUNCTION dbo.ufnReverse;  
GO  
CREATE FUNCTION dbo.ufnReverse(@Value varbinary(8000))  
RETURNS varbinary(8000) 
AS   
-- Returns the stock level for the product.  
BEGIN  
    DECLARE @Ret varbinary(8000);
	Declare @Len int;
	SET @Len = DATALENGTH(@Value);
	SET @Ret = 0x;
	WHILE (@Len >0)
	BEGIN
		Set @Ret = @Ret + SUBSTRING(@Value, @Len, 1);
		Set @Len = @Len - 1;
	END
    RETURN @Ret;  
END; 

當資料庫定序與資料庫服務定序不同時，會發生錯誤

所以建立 Temp Table 的 Varchar or Nvarchar 欄位時，使用 COLLATE DATABASE_DEFAULT

SUSER_SNAME(B.[Transaction SID]) 取代 sys.sysusers Join

展示結果

所以我們用以下的 SQL 來測試

--先建立測試的資料，並刪除它
-- Drop Table tbl_Sample
Create Table tbl_Sample 
([ID] int identity(1,1) ,
[Name] varchar(50),
[Name2] nvarchar(50),
[T1] int,
[Sdate] Datetime)
GO
Insert into tbl_Sample values ('Rec 1',N'堃 亂馬客 Rec1', 11, '2020/09/25')
Insert into tbl_Sample values ('Rec 2',N'堃 亂馬客 Rec2', 22, '2020/01/13') 
Insert into tbl_Sample values ('Rec 3',N'堃 亂馬客 Rec3', 33, '2020/06/14') 
 
Select * From tbl_Sample
Delete From tbl_Sample

執行 Recover_Deleted_Data_Proc 及 Recover_Deleted_Data_With_UID_Date_Time_Proc 的結果如下，

EXEC Recover_Deleted_Data_Proc NULL,'dbo.tbl_Sample'
EXEC Recover_Deleted_Data_With_UID_Date_Time_Proc null,'dbo.tbl_Sample'

程式碼:rainmakerho/RecoverDeletedDataSQL

如果執行中有任何問題，煩請跟我連絡，謝謝大家。

參考資料

SQL Server – How to find Who Deleted What records at What Time
How to recover deleted data from SQL Server
Recover_Deleted_Data_Proc
[SQL]透過 Transaction Log(fn_dblog) 找回被刪除的資料
SQL transaction log articles
SQL Server fn_dblog() Function Details and Example
Inside the Transaction Log file using fn_dblog() and fn_full_dblog()
rainmakerho/RecoverDeletedDataSQL