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.

Then we will take initial backups.

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

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.




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).




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

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.



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.



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

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

Posted In:
Post Details

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.