Post
Ask Question

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.

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:

Functions

Operation Clumn in fn_dblog()

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

fn_dblog()

Such as test.

LOP_INSERT_ROWS

Recover deleted data code

LOP_MODIFY_ROW

LOP modified row

LOP_DELETE_ROWS

LOP deleted row

LOP_BEGIN_XACT

LOP begin xact

Recovery Models

SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.

Three recovery models exist:

  • Simple --- Operations that require transaction log backups are not supported by the simple recovery model.
  • Full, and
  • Bulk-logged.

Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time. By default, when a new database is created within Microsoft SQL, the recovery model option is set to full.

Recovery Option

Restoring the db with the RECOVERY option is the default option for users with FULL backup. However, if you have different types of backups (differential, transactional, etc.), you may need to use the NORECOVERY option to combine the backups.

Even Recover database without Restore

  • RESTORE DATABASE *database_name* WITH RECOVERY

References:


Similar Articles