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.
- 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.
- Once when the database and table is created, values are added to the table.
- You can see the table content from EmpDetails table using the command;
- Now, some of the values of the table are deleted for proceeding with the recovery of deleted data from transaction log and LSNs.
- 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.
- 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’.
- 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.
- 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.
- For converting LSN value 00000014:000000df:0001 to decimal, first split them into three parts and do the conversion.
- 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.
- Proceed with the restoring of the data using STOPBEFOREMARK command. Use the following commands.
CREATE DATABASE RecoverDelete;
–Create a table
CREATE TABLE EmpDetails
Emp_ID INT IDENTITY(1, 1) ,
Emp_Name VARCHAR(55) ,
Emp_Designation VARCHAR(20) )
SET NOCOUNT ON;
–Now Insert data to table EmpDetails
INSERT INTO EmpDetails
VALUES ( ‘Tony’, ‘SE’ )
, ( ‘Richard’, ‘TL’ )
, ( ‘Stephan’, ‘SE’ )
, ( ‘Thomas’, ‘SSE’ )
, ( ‘Dony’, ‘TH’ )
SELECT * FROM EmpDetails;
After viewing the content, take the backup of the transaction log.
Output is as shown:
— delete data from EmpDetails table
DELETE FROM EmpDetails
WHERE Emp_ID <3
Here, deletion is carried out for IDs less than 3. The resultant table is as shown below.
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.
fn_dblog (NULL, NULL)
[Transaction ID] = ‘0000:0000021e’
[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‘.
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.
BACKUP DATABASE RecoverDelete
TO DISK = ‘D:\backup\RecoverDelete.BAK’
RESTORE DATABASE RecoverDelete_copy
FROM DISK = ‘D:\backup\RecoverDelete.bak’
MOVE ‘RecoverDelete’ TO ‘D:\backup\RecoverDelete.mdf’,
MOVE ‘RecoverDelete_log’ TO ‘D:\backup\RecoverDelete_log.ldf’,
–Restore with STOPBEFOREMARK
RESTORE LOG RecoverDelete_copy
DISK = N’D:\backup\RecoverDelete.trn’
STOPBEFOREMARK = ‘lsn: 20000000025300001’
The data will be restored in RecoverDelete_copy and you can check it using the following statement command.
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.
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