Recover Deleted Data From SQL Table Using Transaction Log and LSNs

At times, a user may perform UPDATE operation or DELETE operation in SQL Server database without applying the WHERE condition. This is a very common reason for encountering loss of data from SQL Server tables. As the SQL Server database is a highly popular relational DBMS among corporate sectors and businesses, the data loss problem magnifies even more. So users should be aware of the methods to recover deleted data from SQL Server Table in case of any mishaps.

Deleted rows can be recovered if the time of their deletion is known. This can be done through the use of Log Sequence Numbers (LSNs). LSN is a unique identifier given to every record present in the SQL Server transaction log. The upcoming section will discuss the process to recover deleted SQL Server data and tables with the help of Transaction log and LSNs.

Recommended: For avoiding the long and erroneous manual LSN approach to recover deleted records from SQL table make use of an advanced third-party software such as the SQL Database Repair.

Recover Deleted Data From SQL Server Table by Transaction Logs

There are some prerequisites to be fulfilled before we start the process to recover deleted data from SQL table. To easily recover deleted rows from a table in SQL Server database, it must have the BULK-LOGGED or FULL recovery model at the time when the deletion first occurred. Some quick actions are required so that the logs are still available to perform the recovery of data.

Follow the steps given below to recover deleted data from SQL Server 2005, 2008, 2012, 2014, and 2016 by the use of transaction logs.

Step 1

Check the number of rows present in the table from which the data has been accidentally deleted using the below-mentioned query:

SELECT * FROM Table_name

Step 2

Take the transaction log backup of the database using the query given below:

USE Databasename
GO
BACKUP LOG [Databasename]
TO DISK = N'D:\Databasename\RDDTrLog.trn'
WITH NOFORMAT, NOINIT,
NAME = N'Databasename-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Step 3

In order to recover deleted data from SQL Server Table, we need to collect some information about the deleted rows. Run the query given below to achieve this purpose

USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation  = 'LOP_DELETE_ROWS'

From the query given above, we will obtain the Transaction ID (Let's say 000:000001f3) of the deleted rows. Now, the time when these rows were deleted is to be determined using this ID.

Step 4

In this step, we will find the specific time at which rows were deleted using the Transaction ID 000:000001f3. This is done by executing the query given as follows:

USE Databasename
GO
SELECT
[Current LSN],  Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘000:000001f3'
AND
[Operation] = 'LOP_BEGIN_XACT'

On executing this query we will get the value of current Log Sequence Number (LSN) (let's say 00000020:000001d0:0001).

Step 5

Now we will start the restore process to recover deleted data from SQL Server Table rows that was lost. This is done using the below query:

USE Databasename
GO
RESTORE DATABASE Databasename_COPY FROM
DISK = 'D:\Databasename\RDDFull.bak'
WITH
MOVE 'Databasename' TO 'D:\RecoverDB\Databasename.mdf',
MOVE 'Databasename_log' TO 'D:\RecoverDB\Databasename_log.ldf',
REPLACE, NORECOVERY;
GO

Step 6

Now apply transaction log to restore deleted rows by using LSN 00000020:000001d0:0001:

USE  Databasename
GO
RESTORE LOG Databasename_COPY FROM DISK = N'D:\Databasename\RDOTrLog.trn' WITH STOPBEFOREMARK = ‘lsn:0x00000020:000001d0:0001'  Note: Since LSN values are in Hexadecimal form and for restoring tables using this LSN, we need to convert it into decimal form. For this purpose, we add 0x just before the LSN as shown above.

Step 7

The process to recover deleted records from SQL table will get completed successfully. Now check whether the deleted records are back in the database named Databasename_Copy.

USE Databasename_Copy GO Select * from Table_name

Disadvantages of Transaction Log Approach

• Highly time-consuming method to recover deleted data from SQL Server Table, as it involves several lengthy queries to be executed.
• Extremely complex to implement for users not possessing adequate technical knowledge.
• Greater chances of losing data due to errors while application and execution of queries.

Conclusion

Although the log sequence number method can restore deleted records from SQL tables, it is not a recommended option for users due to its complexity and tediousness. Instead, it is advised to use an automated solution to recover deleted data from SQL Server Table.