Recover Deleted SQL Server Data From Transaction Log And LSNs


Each database created in SQL Server contains a transaction log file to record transactions and the changes made to the transactions. It is marked as the demanding component since during the failure of the system these files helps to bring back the database. With the information stored in transaction log, we can restore the database to specific point.

Suppose, if you have accidentlly deleted data from the database, then don’t get nervous. Here, you will get to know the way to recover the deleted SQL Server data from Transaction Log. Recovering the data is not so challenging, with the support of the transaction log and Log Sequence Number (LSNs) you can recover the data. It is easy to recover the data, if you know at which time you deleted the data and who did it. But, in case of accident deletion, recording the time and date is not feasible. Let us see how to recover deleted data from transaction log and LSNs

Before understanding how to recover deleted record in SQL Server, you might be wondering what is this LSN and how it works.

Log Sequence Number

LSN identifies the record stored in transaction log files and it helps to restore the data back when deleted.

Here, the discussion is carried out by specifying an example; a database called RecoverDelete is created and then, to that database we add an EmpDetails table with up to five employee’s details. Thereafter, deletes the data from the table.

Steps To Recover Deleted Data From Transaction Log

Let us see the process of recovering record in SQL Server stepwise stepwise. These are carried out with the assumption that database is in full recovery mode.

  1. A test database named RecoverDelete in SQL Server is created and to it table EmpDetails is added. The following commands were used for executing these and from the image you can see its result as well.
  2. CREATE DATABASE RecoverDelete;
    GO
    USE RecoverDelete;
    GO

    –Create a table
    CREATE TABLE EmpDetails
    (
    Emp_ID INT IDENTITY(1, 1) ,
    Emp_Name VARCHAR(55) ,
    Emp_Designation VARCHAR(20) )
    GO

    qa-blog-1

  3. Once when the database and table is created, values are added to the table.
  4. SQL Commands:

    SET NOCOUNT ON;
    GO
    USE RecoverDelete;
    GO

    –Now Insert data to table EmpDetails
    INSERT INTO EmpDetails
    VALUES ( ‘Tony’, ‘SE’ )
    , ( ‘Richard’, ‘TL’ )
    , ( ‘Stephan’, ‘SE’ )
    , ( ‘Thomas’, ‘SSE’ )
    , ( ‘Dony’, ‘TH’ )
    GO

    qa-blog-2

  5. You can see the table content from EmpDetails table using the command;
  6. SELECT * FROM EmpDetails;

    After viewing the content, take the backup of the transaction log.

    Output is as shown:

    qa-blog-3

  7. Now, some of the values of the table are deleted for proceeding with the recovery of deleted data from transaction log and LSNs.
  8. — delete data from EmpDetails table
    USE RecoverDelete
    GO
    DELETE FROM EmpDetails
    WHERE Emp_ID <3
    GO

    Here, deletion is carried out for IDs less than 3. The resultant table is as shown below.

    qa-blog-4

    Now, in next step you may see the query which will show information of deleted records in SQL Server and after that we will recover these deleted record.

  9. Next is to proceed with the searching of the transaction log to collect info on deleted items. The following commands are executed for getting the transaction ID.
  10. SELECT
    [Current LSN],
    Operation,
    [Transaction ID],
    [Transaction Name],
    AllocUnitName
    FROM
    fn_dblog (NULL, NULL)

    Output is:

    qa-blog-5

  11. Since the data is deleted, you will get to see the operation LOP_DELETE_ROWS and straight to it the transaction ID. Here, the ID: ‘0000:0000021e’ is same for all the deleted data because the deletion is done in batch. For proceeding with next step we need the transaction ID: ‘0000:0000021e’.
  12. qa-blog-6

  13. Once if the ID is obtained, then next is to find the exact LSN found during the DELETE command. For that, the following commands are executed.
  14. USE RecoverDelete
    GO
    SELECT
    [Current LSN],
    Operation,
    [Transaction ID],
    [Begin Time],
    [Transaction Name]
    FROM
    fn_dblog(NULL, NULL)
    WHERE
    [Transaction ID] = ‘0000:0000021e’
    AND
    [Operation] = ‘LOP_BEGIN_XACT’

    You will get the Current LSN as 00000014:000000df:0001 and it is read as; a DELETE command was executed at time 2015/12/09 13:32:35:687 with LSN 00000014:000000df:0001 and transaction ID ‘0000:0000021e‘.

    qa-blog-7

  15. In-order to get the data recovered, we use STOPBEFOREMARK command. Before moving into the step, you need to convert the hexadecimal LSN value to decimal. You can use online converter for the conversion or do manually, if you know the procedure to convert.
  16. For converting LSN value 00000014:000000df:0001 to decimal, first split them into three parts and do the conversion.
  17. 00000014 decimal value – 20
    000000df decimal value – 253
    0001 decimal value – 1

    The resultant will be obtained as 20000000025300001 by adding leading zeroes with both 253 and 1 to make it as 10-character decimal and 5-character decimal respectively.

  18. Take log backup if the transaction log backup is not taken after the deletion. Before recovering deleted record in SQL Server, transaction log backup is taken.
  19. USE [Master]
    GO
    BACKUP DATABASE RecoverDelete
    TO DISK = ‘D:\backup\RecoverDelete.BAK’
    GO

  20. Proceed with the restoring of the data using STOPBEFOREMARK command. Use the following commands.
  21. RESTORE DATABASE RecoverDelete_copy
    FROM DISK = ‘D:\backup\RecoverDelete.bak’
    WITH
    MOVE ‘RecoverDelete’ TO ‘D:\backup\RecoverDelete.mdf’,
    MOVE ‘RecoverDelete_log’ TO ‘D:\backup\RecoverDelete_log.ldf’,
    REPLACE, NORECOVERY;
    GO
    –Restore with STOPBEFOREMARK
    RESTORE LOG RecoverDelete_copy
    FROM
    DISK = N’D:\backup\RecoverDelete.trn’
    WITH
    STOPBEFOREMARK = ‘lsn: 20000000025300001’

The data will be restored in RecoverDelete_copy and you can check it using the following statement command.

USE RecoverDelete_copy
GO
SELECT * from EmpDetails

You can see above how to recover deleted record in SQL Server and screen shot provided above shows that deleted data get recovered.

Summary

Hope you got an idea about how to recover the deleted data with the aid of LSN and transaction log. The above method is suitable to recover deleted data from transaction log and give the result to users expectation