Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (33)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Administration

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

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.

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)

NOTE:-DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTINGS IN LOWER-LIFE CYCLE ENVIRNOMENTS

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];
GO
CREATE DATABASE ReadingDBLog;
GO

-- Create tables.
USE ReadingDBLog;
GO
CREATE TABLE [Location] (
    [Sr.No] INT IDENTITY,
    [Date] DATETIME DEFAULT GETDATE (),
    [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
INSERT INTO Location DEFAULT VALUES ;
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
Go
DELETE Location 
WHERE [Sr.No] < 10
go
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
go
SELECT 
 [Current LSN],    
 [Transaction ID],
     Operation,
     Context,
     AllocUnitName
    
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    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
go
SELECT
 [Current LSN],    
 Operation,
     [Transaction ID],
     [Begin Time],
     [Transaction Name],
     [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = '0000:0000055e'
AND
    [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.
RESTORE DATABASE ReadingDBLog_COPY
    FROM DISK = 'C:\ReadingDBLog_FULL_15JAN2014.bak'
WITH
    MOVE 'ReadingDBlog' TO 'C:\ReadingDBLog.mdf',
    MOVE 'ReadingDBlog_log' TO 'C:\ReadingDBLog_log.ldf',
    REPLACE, NORECOVERY;
    
    GO

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

   RESTORE LOG ReadingDBLog_COPY
FROM
    DISK = N'C:\ReadingDBlog_tlogbackup_15thJan610.trn'
WITH
    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.

USE ReadingDBLog_COPY
GO
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
GO
DROP TABLE Location

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
GO
SELECT 
[Current LSN],
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
 [Begin Time],
   [SPID],
   Description

FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GO
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.


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, July 05, 2017 - 8:38:39 PM - odlan_yer Back To Top

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

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

Hi,

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 09, 2016 - 6:35:30 AM - chowdary Back To Top

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

Alex 
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

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

 

Regads

Vinod


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

 Hi!

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

Alex

 

 


Tuesday, June 09, 2015 - 8:16:04 AM - pramod Back To Top

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 02, 2015 - 7:38:08 AM - csmenaria Back To Top

hello,

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

 

 


Wednesday, January 07, 2015 - 1:52:12 AM - Dastagiri Dudekula Back To Top

hi,

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


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

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


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

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

RESTORE DATABASE pund330_copy
FROM DISK = 'C:\DB_Backups\pund330.bak'
WITH
MOVE 'pund330' TO 'C:\DB_Backups\pund330.mdf',
MOVE 'pund330_log' TO 'C:\DB_Backups\pund330.ldf',
REPLACE, NORECOVERY;
GO

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. 

RESTORE FILELISTONLY

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

WITH FILE = 2,

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

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

    GO

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 03, 2014 - 7:42:38 AM - Ray Back To Top

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

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

Hari

 

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

 

http://sqlpost.blogspot.com/2009/06/sql-server-working-with-lsn-for-restore.html

 

 


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

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

 

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

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

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


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

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

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

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

SUPERB! You've done a great job. Thanks 


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

 

 

please anybody tells how to recover truncated data

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

Hi Manvendra Signh,

Great Article.

Good Job.

Thanks, regards.


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

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

Finding Out Who Dropped The Table using The Transaction Log:

http://www.sqlskills.com/blogs/paul/finding-out-who-dropped-a-table-using-the-transaction-log/

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

http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/


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

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

 

Excellent topic and rather excellent explanation!!!


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

 

 

please tell how to recover truncated record and tables


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

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

Fabulous article, great read.....


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

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

Thank You


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

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, 
SELECT CONVERT(VARBINARY(8), 16777215)

Thank you for the article, it is very useful!


Learn more about SQL Server tools