Post
Ask Question

How to Recover Deleted/truncated and Dropped Data ,objects in SQL Server Using Transaction Log and LSNs

This article is the most important part for DBAs and developers.
 
Sometimes DBAs and developers delete, truncate and drop data objects from the database. So I decided to recover data and objects using the transaction log and LSNs.
 
This is not as challenging a task to recover the data.  However, you may not be aware of the date and the time when the exact data was deleted/truncated and the dropped data objects from the database. I recommend you read my previous tip  how to Track Database Transaction Log in SQL Server 2012. This tip will help you to understand the Transaction Log.
 
Let's create a small Test Environment using the Transaction Log and LSNs using the undocumented function "fn_dblog”.
 
I assume the database is in full recovery mode. If you want to recover your data and objects I suggest you first use the following procedure on the test environment.
 
Now I will follow the test environment.
 
Step 1
 
Now I will create a database and a table that I will create delete and drop test for as in the following:
  1. --Create Test DB.      
  2. USE [master];      
  3. GO      
  4. CREATE DATABASE RecoverDB;      
  5. GO     
  6.     
  7. USE RecoverDB;      
  8. GO     
  9.  --Create a table     
  10.  CREATE TABLE Employee    
  11.     (    
  12.       Emp_ID INT IDENTITY(1, 1) ,    
  13.       Emp_Name VARCHAR(55) ,   
  14.       Emp_Technology VARCHAR(55) ,    
  15.       Emp_Salary DECIMAL(10, 2) ,    
  16.       Emp_Designation VARCHAR(20) )    
  17. GO    
  18. --Create another table    
  19.  CREATE TABLE Personal_Detail    
  20.     (    
  21.       Emp_ID INT,    
  22.       FName VARCHAR(55) ,    
  23.       DOB VARCHAR(55) ,    
  24.       [Address] VARCHAR(60) ,    
  25.       Mobile INT ,    
  26.       [State] VARCHAR(60) )    
  27.     
  28. GO   
Step 2
 
Now I will take a full backup of the preceding database.
 
Note: If you want to use an existing database then there is no need to take a new full backup for recovery, you just need the last created full backup of the database.
  1. -- Create Backup of 'RecoverDB' database.      
  2. USE [Master]      
  3. GO      
  4. BACKUP DATABASE RecoverDB      
  5. TO DISK = 'D:\backup\RecoverDB.BAK'      
  6. GO 
 
Step 3
 
I have created a database named RecoverDB and a table named Employee and Personal_Detail. Now I will insert a few records in the table.
 
Note: For an existing database there is no need to do this step.
  1. SET NOCOUNT ON;      
  2. GO    
  3. USE RecoverDB;  
  4. GO  
  5.   
  6.  --Now Insert data to table Employee    
  7.  INSERT INTO Employee    
  8.  VALUES ( 'Sumit', 'SQL', 12000, 'SE' )    
  9. ,       ( 'Ravi', 'ASP.NET', 15000, 'TL' )    
  10. ,       ( 'Ajay', 'C#', 14000, 'SE' )    
  11. ,       ( 'vikas', 'JAVA', 22000, 'SSE' )    
  12. ,       ( 'deepak', 'VB', 18000, 'TH' )    
  13.     
  14. -- Now Insert data to Personal_Detail    
  15.  INSERT INTO Personal_Detail    
  16.  VALUES ( 1, 'V.Agarwal', '28-12-1986', 'Noida', 965287985, 'UP' )    
  17. ,       ( 2, 'S.S.kumar', '02-06-1986', 'delhi', 96548200, 'Delhi' )    
  18. ,       ( 3, 'Garav.rai', '30-03-1987', 'Noida', 99922535, 'UP' )    
  19. ,       ( 4, 'A.R.Patak', '19-12-1986', 'Rampur', 808563131, 'UP' )    
  20. ,       ( 5, 'R.shah', '25-09-1985', 'Delhi', 954651423, 'Delhi' )    
  21.     
  22. GO 
 
 
Step 4
 
Now I will delete some rows from the table named Employee and truncate data from the table Personal_Detail. 
  1. -- delete data from Employee table.      
  2. USE RecoverDB      
  3. GO      
  4. DELETE FROM   Employee     
  5.  WHERE Emp_ID <3    
  6. GO    
  7. -- truncate data from Personal_Detail table.      
  8. TRUNCATE TABLE Personal_Detail    
  9. GO 
 
 
Step 5
 
Now I will find the deleted and truncated rows using the Transaction log using the undocumented function "fn_dblog”. Run the code below to get information about all the deleted transactions.
  1. SELECT    
  2. [Current LSN],        
  3. Operation,    
  4. [Transaction ID],    
  5. [Begin Time],    
  6. [Transaction Name],    
  7. [Transaction SID],    
  8. AllocUnitName    
  9. FROM    
  10. fn_dblog (NULL, NULL) 
 
 
Step 6
 
Now I will take the deleted and truncated Transaction IDs like: Deleted-’0000:000004ea’,
 
Truncated: '0000:000004eb'. You can see that in the preceding screen and Find the LSN .
  1. --For Delete
  2. SELECT  
  3. [Current LSN],      
  4. Operation,  
  5. [Transaction ID],  
  6. [Begin Time],  
  7. [Transaction Name],  
  8. [Transaction SID],  
  9. AllocUnitName  
  10. FROM  
  11. fn_dblog (NULL, NULL)  
  12. WHERE  
  13. [Transaction ID] = '0000:000004ea'  
  14. AND  
  15. [Operation] = 'LOP_BEGIN_XACT'  
  16.   
  17. GO  
  18.   -- for truncate
  19. SELECT  
  20. [Current LSN],      
  21. Operation,  
  22. [Transaction ID],  
  23. [Begin Time],  
  24. [Transaction Name],  
  25. [Transaction SID],  
  26. AllocUnitName  
  27. FROM  
  28. fn_dblog (NULL, NULL)  
  29. WHERE  
  30. [Transaction ID] = '0000:000004eb'  
  31. AND  
  32. [Operation] = 'LOP_BEGIN_XACT'  
 
 
Step 7
 
Now I will drop the tables named Employee and  Personal_Detail.
  1. USE RecoverDB        
  2. GO     
  3. DROP TABLE Employee       
  4. USE RecoverDB        
  5. GO    
  6.  DROP TABLE Personal_Detail  
  7. GO    
Step 8
 
Now I will find the dropped tables LSN using the Transaction log using the undocumented function "fn_dblog”. Run the code below to get the information about all the dropped transactions.
  1. --drop tables Name Employee and  Personal_Detail.  
  2. SELECT   
  3. [Current LSN],  
  4. Operation,  
  5. [Transaction Id],  
  6. [Transaction SID],  
  7. [Transaction Name],  
  8.  [Begin Time],  
  9.    [SPID],  
  10.    Description  
  11.   
  12. FROM fn_dblog (NULL, NULL)  
  13. WHERE [Transaction Name] = 'DROPOBJ'  
  14. GO  
 
 
Role of Log Sequence number(LSN)
 
Log Sequence Number (LSN) has uniquely identified each record in the SQL Server transaction log. The LSN of a log record at which a significant event occurred can be useful for constructing the 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. For more details Overview to Log Sequence Numbers.
 
Step 9
 
You can see in the receding LSN for Delete and Truncate. To recover data I will be using the STOPBEFOREMARK operation. The LSN value is in hexadecimal format. If I use the STOPBEFOREMARK operation then I need to change this LSN value into decimal format to make a single number.
 
You can see in the receding screen the LSN value for deleted is 00000035:000000b1:0001; this value is in three parts.
 
I can be divide them as: part A (00000035), B (000000b1), C (0001). Now there is a separate process to convert the LSN numbers. You can covert them using Hex to Decimal Converter.
 
Or use a SQL Server command like
  1. SELECT CAST (CONVERT (VARBINARY,'0x'+'Values', 1) AS INT)  
For example, for a deleted LSN: 00000035:000000b1:0001.
 
Using Hex to Decimal Converter
 
 
 
You can  get the same as in the above using Hex. 
 
Using SQL Server
  1. --For Deleted table Employee  
  2. SELECT CAST (CONVERT (VARBINARY,'0x'+'00000035', 1) AS INT) as A,   
  3. CAST (CONVERT (VARBINARY,'0x'+'000000b1', 1) AS INT) as B,   
  4. CAST (CONVERT (VARBINARY,'0x'+'0001', 1) AS INT)as C    
  5. GO  
  6. --For Truncated table Persona_Detail  
  7. SELECT CAST (CONVERT (VARBINARY,'0x'+'00000035', 1) AS INT) as A,   
  8. CAST (CONVERT (VARBINARY,'0x'+'000000b3', 1) AS INT) as B,   
  9. CAST (CONVERT (VARBINARY,'0x'+'0001', 1) AS INT)as C    
  10. GO  
  11. --For Dropped table Employee  
  12. SELECT CAST (CONVERT (VARBINARY,'0x'+'00000035', 1) AS INT) as A,   
  13. CAST (CONVERT (VARBINARY,'0x'+'000000b9', 1) AS INT) as B,   
  14. CAST (CONVERT (VARBINARY,'0x'+'0001', 1) AS INT)as C    
  15. GO  
  16. --For Dropped table Persona_Detail  
  17. SELECT CAST (CONVERT (VARBINARY,'0x'+'00000035', 1) AS INT) as A,   
  18. CAST (CONVERT (VARBINARY,'0x'+'000000ca', 1) AS INT) as B,   
  19. CAST (CONVERT (VARBINARY,'0x'+'0001', 1) AS INT)as C    
  20. GO  
 
 
Let's start with part A.  I will convert this value to decimal with no leading zeros, that will be 53. Now convert part B to a 10-character decimal number, including leading zeroes, that will be 0000000117. Now convert part C to a 5-character decimal number, including leading zeros, that will be 00001.
 
Now we have a decimal value of part A, part B and part C. Put them together as one single number like this:

  • Deleted: 53000000011700001
  • Truncated: 53000000017900001
  • Drop table Employee: 53000000018500001
  • Drop table Personal_Detail: 53000000020200001
Step 8: Now I will take a Transaction Log Backup using the following script:
  1. --Create Log Backup  
  2. USE RecoverDB        
  3. GO    
  4. BACKUP LOG RecoverDB TO DISK = 'D:\backup\RecoverDB.TRN'  
  5. GO  
 
 
Step 9
 
Now I will restore a copy of the RecoverDB database and restore a Log backup with the STOPBEFOREMARK option to recover the exact LSN.
  1. --Restoring Full backup with norecovery.  
  2. RESTORE DATABASE RecoverDB_copy  
  3.     FROM DISK = 'D:\backup\RecoverDB.bak'  
  4. WITH  
  5.     MOVE 'RecoverDB' TO 'D:\backup\RecoverDB.mdf',  
  6.     MOVE 'RecoverDB_log' TO 'D:\backup\RecoverDB_log.ldf',  
  7.     REPLACE, NORECOVERY;  
  8.       
  9.     GO  
  10. Example 1
  11. --Restore Log backup with STOPBEFOREMARK option to recover exact LSN.  
  12.    RESTORE LOG RecoverDB_copy  
  13. FROM  
  14.     DISK = N'D:\backup\RecoverDB.trn'  
  15. WITH  
  16.     STOPBEFOREMARK = 'lsn:53000000011700001'
  17.   
  18.    
 
 
Now you can get the deleted data in RecoverDB_copy. 
 
 
 
You can follow the same process as in the preceding from STEP 9 that I followed in the first example. This way we can recover the truncated and drop data objects from your database.
 
You can see in the screen above that I have recovered the deleted data from the Employee table using the Transaction Log and LSNs.
 
Summary
 
This article described how to recover deleted, truncated and dropped data objects using the transaction log and LSNs in SQL Server.


Similar Articles