Recover Deleted Data from Transaction Log Backup File using fn_dump_dblog() function

A transaction log is a physical file in which SQL Server stores a record of all the transactions performed on the database with the log file is associated. SQL Server automatically creates a transaction log for every new database. The database recovery model has to be Full or Bulk-logged when you take transaction log backup. In this article, it will be demonstrated how to recover deleted data from transaction log backup files. We will use fn_dblog() function to read the transaction log for finding the deleted data that haven’t been cleared from the active part of the transaction log. After the Transaction Log will get clear, we will try to recover deleted data from a Transaction Log backup file using fn_dump_dblog().

fn_dblog() function is an undocumented function that reads the active portion of an online transaction log

Firstly, we will create a database and a table with default values to prepare our demo environment.

USE [master]

IF DB_ID('LOGDEMO1') IS NOT NULL

BEGIN

ALTER DATABASE [LOGDEMO1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [LOGDEMO1]

END

CREATE DATABASE [LOGDEMO1]

ON PRIMARY

( NAME = N'LOGDEMO1', FILENAME = N'D:\Workload\Databases\SQL2014\LOGDEMO1_Data.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'LOGDEMO1_log', FILENAME = N'D:\Workload\Databases\SQL2014\LOGDEMO1_Log_ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

ALTER DATABASE [LOGDEMO1] SET RECOVERY FULL

USE [LOGDEMO1]

CREATE TABLE dbo.PurchaseOrders

(

OrderId INT IDENTITY (1000, 1),

OrderDate DATETIME DEFAULT GETDATE(),

OrderItem VARCHAR(25) DEFAULT 'MyItem',

LineItem VARCHAR(25) DEFAULT 'MyLineItem'

)

Then we will take initial backups.

BACKUP DATABASE [LOGDEMO1]

TO DISK = N'D:\Workload\Databases\SQL2014\LOGDEMO1_FULL.bak' WITH INIT

BACKUP LOG [LOGDEMO1]

TO DISK = N'D:\Workload\Databases\SQL2014\LOGDEMO1_LOG_1.trn' WITH INIT

Let’s try to apply modifications within different time frames. This will help to identify at what time (minute) the rows are deleted.

CREATE CLUSTERED INDEX IDX_OrderId ON dbo.PurchaseOrders(OrderId);

WAITFOR DELAY '00:01:00'

INSERT INTO PurchaseOrders DEFAULT VALUES;

GO 5

DROP INDEX IDX_OrderId ON dbo.PurchaseOrders

WAITFOR DELAY '00:01:00'

INSERT INTO PurchaseOrders DEFAULT VALUES;

GO 5

DELETE FROM dbo.PurchaseOrders WHERE OrderId IN (1005, 1006, 1007, 1008)

Above code shows which create/drop index and insert/delete data statements are used. Now, we need to examine begin time and LSN of deleted data. Let’s try to list.

1 2	SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time] FROM fn_dblog(NULL, NULL) WHERE [Transaction Name] IN ('CREATE INDEX', 'DROP INDEX', 'DELETE')

On the next step, we need to ensure LSN numbers are continuing and count of the LSN is equel to our delete row count (we deleted 4 rows).

SELECT [Current LSN], [Transaction ID], [Operation], [AllocUnitName]

FROM fn_dblog(NULL, NULL)

WHERE [Transaction ID] = '0000:0000031e' AND [Operation] = 'LOP_DELETE_ROWS' AND [AllocUnitName] = 'dbo.PurchaseOrders'

Now, insert few new rows and take another backup to clear the current transaction log.

INSERT INTO PurchaseOrders DEFAULT VALUES;

GO 100

WAITFOR DELAY '00:01:00'

BACKUP LOG [LOGDEMO1]

TO DISK = N'D:\Workload\Databases\SQL2014\LOGDEMO1_LOG_2.trn' WITH INIT

When you want to list the Transaction ID of “0000:0000031e” there will be no results. It means active portion of the Transaction Log is backed up and cleared. So, let’s use fn_dump_dblog function to read the Transaction Log backup file for finding deleted rows.

SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time]

FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'D:\Workload\Databases\SQL2014\LOGDEMO1_LOG_2.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

WHERE [Transaction Name] IN ('DELETE')

Yes! we find the LSN number of deleted rows to use on STOPBEFOREMARK option which is to restore the Transaction Log backup. But, we missed something. We find hexadecimal number of LSN. STOPBEFOREMARK option requires decimal numbers. We need to convert these numbers from hexadecimal to decimal. Let’s add some codes to convert hexadecimal numbers.

SELECT CAST(CAST(CONVERT(VARBINARY, LEFT([Current LSN], 8), 2 ) AS INT) AS VARCHAR) +

RIGHT('0000000000' + CAST(CAST(CONVERT(VARBINARY, SUBSTRING([Current LSN], 10, 8), 2) AS INT) AS VARCHAR), 10 ) +

RIGHT('00000' + CAST(CAST(CONVERT(VARBINARY, RIGHT([Current LSN], 4), 2 ) AS INT) AS VARCHAR), 5) AS [Decimal LSN]

, [Transaction Name], [Begin Time]

FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'D:\Workload\Databases\SQL2014\LOGDEMO1_LOG_2.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

WHERE [Transaction Name] IN ('DELETE')

Now, we can restore the backup files. We will use STOPBEFOREMARK option when we restore the second Transaction Log backup.

USE [master]

IF DB_ID('LOGDEMO1_Restored') IS NOT NULL

BEGIN

ALTER DATABASE [LOGDEMO1_Restored] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [LOGDEMO1_Restored]

END

RESTORE DATABASE [LOGDEMO1_Restored]

FROM DISK = N'D:\Workload\Databases\SQL2014\LOGDEMO1_FULL.bak'

WITH FILE = 1,

MOVE N'LOGDEMO1' TO N'D:\Workload\Databases\SQL2014\LOGDEMO1_Data_Restored.mdf',

MOVE N'LOGDEMO1_log' TO N'D:\Workload\Databases\SQL2014\LOGDEMO1_Log_Restored.ldf',

NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG [LOGDEMO1_Restored]

FROM DISK = N'D:\Workload\Databases\SQL2014\LOGDEMO1_LOG_1.trn'

WITH NORECOVERY

RESTORE LOG [LOGDEMO1_Restored]

FROM DISK = N'D:\Workload\Databases\SQL2014\LOGDEMO1_LOG_2.trn'

WITH STOPBEFOREMARK = 'lsn:33000000040800001', NORECOVERY

RESTORE DATABASE [LOGDEMO1_Restored] WITH RECOVERY

Here we go. We manage to recover our deleted records.

USE [LOGDEMO1_Restored]

SELECT * FROM dbo.PurchaseOrders WHERE OrderId IN (1005, 1006, 1007, 1008)

Tags: Recover, T-LOG, TLOG, Transaction Log

Posted On: 06/07/2014

Posted In:

Technical

0 Comments

Written By: yigitaktan

Post Details

Recover Deleted Data from Transaction Log Backup File using fn_dump_dblog() function

Leave a Reply Cancel reply