Recover deleted SQL Server data and tables with the help of Transaction Log and LSNs

By:   |   Comments (36)   |   Related: 1 | 2 | 3 | 4 | More > Database Administration


My last tip was about how to find a user who ran a DROP or DELETE statement on your SQL Server objects. Now we will learn how to recover deleted SQL Server data with the help of the transaction log and the respective LSN numbers. This tip will give you step by step process to achieve this solution.


I have already discussed reading a SQL Server Transaction log and finding a user who has deleted or dropped SQL Server objects in my last two tips. I recommend reading my previous tips to understand how the transaction log is responsible for logging all database activities. In this tip, we will use the same undocumented function "fn_dblog" to find any unauthorized data deletion and recover the deleted data. For our examples, I assume the database is in full recovery mode.  I also strongly suggest testing any undocumented code in a lab environment prior to production when the need arises.

If you know when your data was deleted, then it is not as challenging a task to recover the data.  However, if you are not aware of the date and time when a particular data was deleted, then we will find out in a similar way as we determined who deleted data from the database in my last tip. Here we will first find the exact LSN under which the delete statement ran and then we will recover data until that LSN.

As per SQL Server Books Online "Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). The LSN of a log record at which a significant event occurred can be useful for constructing correct restore sequences. LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN." ( Introduction to Log Sequence Numbers)


Restoring deleted SQL Server data with the help of Log Sequence Number (LSN)

Step 1: Before moving ahead, we will create a database and a table on which I will run a DELETE statement and then we will find the deleted data and recover it. Run the T-SQL code below to create a database and table.

--Create DB.
USE [master];

-- Create tables.
USE ReadingDBLog;
CREATE TABLE [Location] (
    [City] CHAR (25) DEFAULT 'Bangalore');

Step 2: We have created a database named "ReadingDBLog" and a table 'Location' with three columns. Now we will insert a few rows in the table.

USE ReadingDBLog
GO 100

Step 3: Now we will go ahead and delete some rows to recover them with the help of LSNs in the SQL Server Transaction Log.

USE ReadingDBLog
DELETE Location 
WHERE [Sr.No] < 10
select * from Location
Delete rows from the table'location'

You can see in above screenshot that rows less than 10 have been deleted from the table "Location".

Step 4: Now we have to search the SQL Server Transaction Log to get the information about the deleted rows. Run the code below to get information about all deleted transactions.

use ReadingDBLog
 [Current LSN],    
 [Transaction ID],
    fn_dblog(NULL, NULL) 
    Operation = 'LOP_DELETE_ROWS'
Find all the deleted rows info from t-log file

We can see transaction IDs under which DELETE statement was ran in above screenshot. As we are aware that our data is deleted from the Location table, we will concentrate on only those transaction IDs under which the data is deleted from the location table. We can find the table name from "AllocUnitName" column. It says a DELETE statement has been performed on a HEAP table 'dbo.Location' under transaction ID 0000:0000055e. As there is only one transaction ID under which all DELETED rows are showing that means this action has been performed in a single batch. Now capture the transaction ID from here for our next command.

Step 5: We found the transaction ID from above screenshot/script which we will use in the below statement to get the LSN of the LOP_BEGIN_XACT operation. That means we need the exact LSN when the DELETE statement began its transaction.

USE ReadingDBLog
 [Current LSN],    
     [Transaction ID],
     [Begin Time],
     [Transaction Name],
     [Transaction SID]
    fn_dblog(NULL, NULL)
    [Transaction ID] = '0000:0000055e'
    [Operation] = 'LOP_BEGIN_XACT'
Find the LSN of the deleted item

Here, we can see the LSN of this transaction. We can read above output like " A DELETE statement was started at 2014/01/15 17:54:38:347 with LSN 00000016:000001a5:0001 under transaction ID 0000:0000055e". Now our next step is to change the hexadecimal value of this LSN to decimal in order to recover our data.

Step 6: We will be using the STOPBEFOREMARK operation to recover our data. We cannot use this hexadecimal value to run the STOPBEFOREMARK operation so we need to change this value into decimal format and then put it together as a single number. I used this online Hex to Decimal Converter to change this LSN to decimal.

The LSN we have from the above screenshot is 00000016:000001a5:0001. Here we can see there are three parts. We can divide them as part A (00000016), part B (000001a5) and part C (0001). Now there is a separate process to convert the numbers. Let's start with part A.  We will convert this value to decimal with no leading zero which will be 22. Now convert part B to a 10-character decimal number, including leading zeroes, which will be 0000000421. Now convert part C to a 5-character decimal number, including leading zero, which will be 00001.

Now we have decimal value of part A, part B and part C. Put them together as one single number like this: 22000000042100001.

Step 7: Now run a transaction log backup on the database where the data was deleted if a transaction log backup has not ran since the data deletion. Next, we will restore this database to somewhere else or on same server with different name until the above LSN and then we will import the deleted data from newly restored database to your production database.

--Restoring Full backup with norecovery.
    FROM DISK = 'C:\ReadingDBLog_FULL_15JAN2014.bak'
    MOVE 'ReadingDBlog' TO 'C:\ReadingDBLog.mdf',
    MOVE 'ReadingDBlog_log' TO 'C:\ReadingDBLog_log.ldf',

--Restore Log backup with STOPBEFOREMARK option to recover exact LSN.

    DISK = N'C:\ReadingDBlog_tlogbackup_15thJan610.trn'
    STOPBEFOREMARK = 'lsn:22000000042100001'

Step 8: Now your database is restored and you can check whether your deleted data is recovered or not. Run the below SELECT statement to see the first ten rows of the location table which was deleted.

SELECT * from Location
Checking whether data is recovered in table or not

Now you can see all deleted rows have been recovered and showing in above screenshot. Now you can use this deleted data as per your requirement. If you want to export this data to your live database you can use the SQL Server Integration Services import and export wizard to do that.

Recovery of SQL Server table if its dropped

Step 1: We have demonstrated how to recover deleted data, now what if someone has dropped the whole table? Now I will show you how to recover the entire table. Now consider same example and location table, which I will drop in this example.  Go ahead and run the below commands to drop the "Location" table.

USE ReadingDBLog

Step 2: As was the case with our first example, if you drop a table or you perform any transactions in your database, they will get logged in SQL Server Transaction Log which will be accessed by using the fn_dblog function. Run the below script to display entries which have been logged as a DROP statement.

USE ReadingDBLog
[Current LSN],
[Transaction Id],
[Transaction SID],
[Transaction Name],
 [Begin Time],

FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
Get LSN of dropped object

Step 3: Now you have the LSN of the begin transaction under which table was dropped. Now you can follow same process from above from STEP: 6 which we have followed in the first example.  This way we can recover the unknown deleted data from your database.

Next Steps
  • Use the fn_dblog function to do more research into your SQL Server Transaction Log. There is lot of informative data in more than 100 columns from this function. Just keep in mind you need to look into the issue and correlate it with other data.
  • Build your knowledge with the SQL Server Database Administration Tips.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, August 17, 2018 - 10:47:34 AM - Alavudeen Back To Top (77219)


Good Article...

If a user took a full and log backup after delete or drop and with some other transactions like insert or update , then how could we find the lsn of delete statement thru fn_dblog?

Sunday, June 24, 2018 - 8:22:00 AM - Deepti Back To Top (76338)

Hi i have followed above steps: - I Am getting Variety of errors during execution of this statement 

RESTORE LOG ReadingDBLog_COPY FROM DISK = N'C:\ReadingDBlog_tlogbackup_15thJan610.trn' WITH STOPBEFOREMARK = 'lsn:22000000042100001'

The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.

RESTORE LOG is terminating abnormally.

i restored the database with recovery using command 


Below error is coming in that case  :- The log or differential backup cannot be restored because no files are ready to rollforward. RESTORE LOG is terminating abnormally.

i am unable to proceed further.


Tuesday, December 26, 2017 - 6:59:15 AM - Sriram Back To Top (74469)

Hi bro


   I have followed your tutorial and implemented as same. But i got errors like below. Please go through them.Please help me.


Msg 3201, Level 16, State 2, Line 2

Cannot open backup device 'C:\ReadingDBLog_FULL_15JAN2014.bak'. Operating system error 2(The system cannot find the file specified.).

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

Msg 3201, Level 16, State 2, Line 13

Cannot open backup device 'C:\ReadingDBlog_tlogbackup_15thJan610.trn'. Operating system error 2(The system cannot find the file specified.).

Msg 3013, Level 16, State 1, Line 13

RESTORE LOG is terminating abnormally.






Wednesday, July 5, 2017 - 8:38:39 PM - odlan_yer Back To Top (58936)

Thank you so much for this article.  It helps me solved my problem.  GOD Bless

Tuesday, February 21, 2017 - 11:10:17 PM - tom Back To Top (46576)

I just try to implemented the codes you post, but in the step7, I can not get through.

especially, DISK = N'C:\ReadingDBlog_tlogbackup_15thJan610.trn', in fact , there are .mdf file,

and .ldf file  but no file named .trn.  

So, how did N'C:\ReadingDBlog_tlogbackup_15thJan610.trn' this file be created?  and when ?


Sunday, December 25, 2016 - 2:28:16 AM - Masum Reza Back To Top (45045)


Here I have one doubt.

You are restoring the deleted data with the help of LSN, But on above query you are not taking any backup files.

So, how can we get the datas.


Friday, December 9, 2016 - 6:35:30 AM - chowdary Back To Top (44930)

i took full backup on sunday then someone deleted some rows in my table and did some inserts on that now i need to get all the data with previous data plus recently committed data is it possible to restore both

Tuesday, October 18, 2016 - 6:09:57 AM - Charan Back To Top (43584)

Did you got solution for your problem?

I have db which was never backed(full) up before delete operation performed. So i am looking for recovering data deom log file of that db in SQL 2008 R2 enterprise edition.

Thursday, September 15, 2016 - 2:22:18 AM - vinodjoshi Back To Top (43325)

Great Work!! Hats off to you..many many thanks for sharing




Wednesday, May 11, 2016 - 9:30:31 PM - Alex Back To Top (41462)


I found your Tutorial extremely helpful and detailed, unfortunately I've not been able to fully recover the update since missing a previous backup: in fact as last step got this error:

Msg 4335, Level 16, State 2, Line 63
The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
Msg 3013, Level 16, State 1, Line 63
RESTORE LOG is terminating abnormally.

But googleing around I found that some disaster recovery software as ApexSQL Log do allow to recover the changes directly from log file and active DB (I suppose), in any case surely without requiring a previous backup and to restore the database

Therefore I suppose there is a path to get same result: can give some hints?
Then I will try to reach the goal.

Thanks a lot




Tuesday, June 9, 2015 - 8:16:04 AM - pramod Back To Top (37864)

Hi I am really confused with the step no 7 if it is possible could you please explain more about that step more...

Saturday, May 2, 2015 - 7:38:08 AM - csmenaria Back To Top (37086)


the query that you provided for view transaction log its not displaying the TL. because my data is deleted 5 days ago.



Wednesday, January 7, 2015 - 1:52:12 AM - Dastagiri Dudekula Back To Top (35849)


is there any difference point in time recovery and stopbefore bookmark...

Friday, October 10, 2014 - 4:43:24 PM - taiba Back To Top (34928)

Merci ça m'a vraiment aidé...

Friday, May 30, 2014 - 5:08:48 AM - Veer Back To Top (31990)

Hi I follwed all the steps till I get the belwo error message

FROM DISK = 'C:\DB_Backups\pund330.bak'
MOVE 'pund330' TO 'C:\DB_Backups\pund330.mdf',
MOVE 'pund330_log' TO 'C:\DB_Backups\pund330.ldf',

Message: Logical file 'pund330' is not part of database 'pund330_copy'. Use RESTORE FILELISTONLY to list the logical file names.

So to over this I executed the belwo query. 


    FROM DISK = 'C:\Backup\pund330.bak'


    MOVE 'pund330' TO 'C:\pund330.mdf',

    MOVE 'pund330_log' TO 'C:\pund330_log.ldf';


Message: One or more of the options (move) are not supported for this statement. Review the documentation for supported options. 


Can you please let me know how do I overcome these errors? 

Thursday, April 3, 2014 - 7:42:38 AM - Ray Back To Top (29958)

I would like to know if it is possible to restore a deleted record/s or object from the transaction log rather than creating a database to restore from.  The example you show works great if the database is small but what if the database was very big?  If there is a way to do this I would like to know how it is done.

Saturday, March 15, 2014 - 11:33:15 AM - hari Back To Top (29774)

Hi siva,  I followed all the steps as mentioned in post. I taken backup after delete

Friday, March 14, 2014 - 11:27:56 AM - Siva Back To Top (29762)



if you haven't got the answer please refer this blog which answer your question



Friday, March 14, 2014 - 11:26:09 AM - Siva Back To Top (29761)

Manvendra Singh


Good article. Thanks. When is full database backup is taken after delete or before delete operations. I believe before the delete operations. if that case the bak file will contain the records deleted. Can you let me know when the FULL backup was taken. Thanks in advance.





Friday, February 28, 2014 - 10:10:25 PM - hari Back To Top (29620)


nikhar srivastava  


please anybody tells how to recover truncated data

when data is truncated, it will not be loged in log file. So data can not be recovered.

Thursday, February 27, 2014 - 10:09:15 PM - hari Back To Top (29606)

hi Manvendra,

                Thank you for this tip. But I am getting this error. I taken full backup first and then tlog backup. Even tried to take tlog with no_truncate. please up


Msg 4335, Level 16, State 2, Line 63

The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.

Msg 3013, Level 16, State 1, Line 63

RESTORE LOG is terminating abnormally.

Friday, February 21, 2014 - 1:14:21 PM - Sowmya Back To Top (29532)

Awesome.. !!!!!!!!!!!!!! very very useful... Thanks for sharing...

Monday, February 17, 2014 - 11:26:53 PM - Kingston Back To Top (29482)

Useful article... Is there anyway to recover the deleted rows if the recovery model is simple?

Monday, February 17, 2014 - 8:28:25 AM - PRITESH Back To Top (29471)

Nice Article ... After long I found the article which I needed most ........


Can you please tell me one thing when we restore the log file It required same amount of hard disk space  ? 

Wednesday, February 12, 2014 - 9:03:50 AM - Asif Iqbal Back To Top (29418)

I read also Paul's article now But descirbe it detail with snap shots.

Nice Article.Good

Wednesday, February 12, 2014 - 7:48:39 AM - Chhavi_MCITP Back To Top (29415)

SUPERB! You've done a great job. Thanks 

Tuesday, February 11, 2014 - 10:04:26 AM - nikhar srivastava Back To Top (29405)



please anybody tells how to recover truncated data

Tuesday, February 11, 2014 - 7:46:06 AM - Junior Galv„o - MVP Back To Top (29402)

Hi Manvendra Signh,

Great Article.

Good Job.

Thanks, regards.

Tuesday, February 11, 2014 - 7:16:16 AM - Divine Flame Back To Top (29401)

Here are the links for Paul's article mentioned in the previous comment:

Finding Out Who Dropped The Table using The Transaction Log:

Recovering deleted data & table using FnDBLog & Restoring With StopBeforeMark To AN LSN

Tuesday, February 11, 2014 - 7:11:45 AM - Divine Flame Back To Top (29400)

Paul Randall has written great articles years ago on both these subjects i.e. Finding out who dropped the table & Recovering deleted data & table using Transaction Log and LSNs.

Therefore, I think Paul should have been given the attribute somewhere in the article.

We see dupicate information on different sites quite frequently these days..

Tuesday, February 11, 2014 - 12:21:24 AM - Pravin Back To Top (29398)


Excellent topic and rather excellent explanation!!!

Monday, February 10, 2014 - 1:42:20 PM - nikhar srivastava Back To Top (29395)



please tell how to recover truncated record and tables

Monday, February 10, 2014 - 1:11:56 PM - Deepak Kumar Back To Top (29393)

Marvellous Article Manvendra. I am always Good Fan of your excellent articles.


Thanks once again.

Monday, February 10, 2014 - 11:57:02 AM - Edward Pochinski Back To Top (29391)

Fabulous article, great read.....

Monday, February 10, 2014 - 11:45:28 AM - Jose Back To Top (29390)

*** Great article.... Keep add more. ****

Thank You

Monday, February 10, 2014 - 6:20:54 AM - LEON VENEDIKTOU Back To Top (29386)

Footnote, just to help out on hex to int covnerting.

-- HEX to INT, add the 0x infrond of the number quoted in the article.
select convert(int, 0x000001a5)

-- INT to Hex, 

Thank you for the article, it is very useful!

get free sql tips
agree to terms