Database Recovery (1-2-1): Use Transaction Log to Recover the Deleted or Modified Data

This article is a sub-topic of the previous article, Recover Deleted Table Data "without Backup" in SQL Server, So we have the structure of articles like this.

• Database Recovery (1): Clone Database From One Instance To Another In SQL Server
  • Database Recovery (1-1): Restore a Table from a Backup Database through Restore/Export
  • Database Recovery (1-2): Recover Deleted Table Data "without Backup" in SQL Server
    • Database Recovery (1-2-1): Use Transaction Log to Recover the Deleted or Modified Data --- this article

Introduction

In the previous article, Recover Deleted Table Data "without Backup" in SQL Server, we recovered table records with LSN (Log Sequence Number). In this article, we will discuss a little bit more detail about LSN, the Retrieved function fn_dblog(), and the related Recovery Models.

• Introduction
• SQL Server Transaction Log
• LSN Retrieving Function from Transaction Log
• Operation Clumn in fn_dblog()
• Recovery Models
• Recovery Option

SQL Server Transaction Log

Every SQL Server database has a transaction log that records all transactions and the database modifications that are made by each transaction. The transaction log is a critical component of the database, and if there's a system failure, the transaction log might be required to bring your database back to a consistent state.

The SQL Server Transaction Log plays an important role in recovering deleted or modified data if you mistakenly perform a DELETE or UPDATE operation with the wrong condition or badly without filtration condition. This can be achieved by listening to the records stored inside the SQL Server Transaction Log file. The events that are written to the SQL Server Transaction Log file, without any additional configuration required from the database administrator side, include the different types of DML operations, such as INSERT, UPDATE, and DELETE statements, and DDL operations, such as CREATE and DROP statements.

LSN Retrieving Function from Transaction Log

Function fn_dblog()

The fn_dblog() function is one of several undocumented functions for SQL Server; it allows you to view the transaction log records in the active part of the transaction log file.

fn_dblog() Parameters

The fn_dblog() function accepts two parameters.

• The first is the starting log sequence number or LSN. You can also specify NULL, which means it will return everything from the start of the log.
• The second is the ending LSN. You can also specify NULL, which means you want to return everything to the end of the log.

fn_db_log()
SELECT * FROM fn_dblog (
              NULL, -- Start LSN nvarchar(25)
              NULL  -- End LSN nvarchar(25)
       )

We will not discuss the details of the function but list the possibly useful columns below:

Operation Clumn in fn_dblog()

The ‘Operation’ column indicates the type of operation that has been performed and logged in the transaction log file.

Such as test.

LOP_INSERT_ROWS

LOP_MODIFY_ROW