Post
Ask Question

Recover Deleted Data On SQL Server

Today, I will tell you how to recover the data, in the case of accidental deletion of the data. This operation is tested on Enterprise Edition 2012.

Please follow the steps and execute the next step only when the previous step is finished.

Step 1

Create a new fresh database and a table inside this database.

  1. USE master  
  2. GO  
  3.   
  4. CREATE DATABASE TestDb  
  5. GO  
  6.   
  7. USE TestDb  
  8. GO  
  9.   
  10. CREATE TABLE TestTable (  
  11.  id INT identity(1, 1)  
  12.  ,NAME VARCHAR(50)  
  13.  ,Value INT  
  14.  ,ValueAt DATETIME DEFAULT(GetDate())  
  15.  )  
  16. GO  
Step 2

Create a full backup of this database.
  1. BACKUP DATABASE TestDb TO DISK = 'D:\TestDb_Full.bak'  
  2. WITH init  
  3.  ,format  
  4.  ,stats = 10  
  5. GO  
Step 3

Follow the below DML on table dbo.TestTable.
  1. USE TestDb  
  2. GO  
  3.   
  4. INSERT INTO TestTable ( NAME,Value)  
  5. SELECT 'Vimal' ,50  
  6. UNION  
  7. SELECT 'Kamal' ,250  
  8. UNION  
  9. SELECT 'Raj' ,1200  
  10. UNION  
  11. SELECT 'Swastika',150  
  12. UNION  
  13. SELECT 'Sibin' ,125  
  14. UNION  
  15. SELECT 'Manas' ,560  
  16. UNION  
  17. SELECT 'Amit' ,3200  
  18. GO  
  19.   
  20. SELECT * FROM TestTable  
  21. GO  
  22.   
  23. DELETE FROM TestTable WHERE id > 5  
  24. GO  
  25.   
  26. SELECT * FROM TestTable  
  27. GO  
 
Step 4

As the data has been deleted, now it's time to recover that data.

Note - Here, database is new, so is the table. It is easy to recover. If you know the time of the deleted data or approximate time, you can recover the data easily. But, you will find it difficult if the time range is too long or you have forgotten the time.
  1. SELECT [Current LSN]  
  2.  ,[Transaction ID]  
  3.  ,[Operation]  
  4.  ,[Context]  
  5.  ,[AllocUnitName]  
  6. FROM fn_dblog(NULL, NULL)  
  7. WHERE [Operation] = 'LOP_DELETE_ROWS'  
  8.  AND [AllocUnitName] = 'dbo.TestTable'  
  9.   
  10. SELECT [Current LSN]  
  11.  ,[Operation]  
  12.  ,[Transaction ID]  
  13.  ,[Begin Time]  
  14.  ,[Transaction Name]  
  15.  ,[Transaction SID]  
  16.  ,[AllocUnitName]  
  17. FROM fn_dblog(NULL, NULL)  
  18. WHERE [Transaction ID] = '0000:00000343'--<<Paste the TransactionID  
  19.  AND [Operation] = 'LOP_BEGIN_XACT'  
  1. --Copy CurrentLSN 00000021:000000b0:0001  
  2. SELECT CONVERT(INT, CONVERT(VARBINARY, '0x00000021', 1)) --Same Value  
  3. SELECT CONVERT(INT, CONVERT(VARBINARY, '0x000000b0', 1)) --Total 10 digit preceeded by 0  
  4. SELECT CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) --Total 5 digit preceeded by 0  
  5.   
  6. -->33000000017600001  
  7. SELECT Cast(CONVERT(INT, CONVERT(VARBINARY, '0x00000021', 1)) AS VARCHAR(5)) + RIGHT('0000000000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x000000b0', 1)) AS NVARCHAR), 10) + RIGHT('00000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) AS NVARCHAR), 5) AS 'MarkPoint'  
  8. GO  

Step 5

Take a log backup now. For this, the recovery model of database should be Full or Bulk-logged (Right click on Database>>Properties>>Options>>Recovery Model).
  1. BACKUP log TestDb TO DISK = 'D:\TestDb_log.trn'  
  2. GO  
Step 6

Restore Full backup of database.
  1. -- Starting first with restoring the FULL BACKUP with NORECOVERY  
  2. RESTORE filelistonly  
  3. FROM DISK = 'D:\TestDb_Full.bak';  
  4.   
  5. RESTORE DATABASE [TestDb_New]  
  6. FROM DISK = 'D:\TestDb_Full.bak'  
  7. WITH MOVE 'TestDb' TO 'C:\TestDb.mdf'  
  8.  ,MOVE 'TestDb_log' TO 'C:\TestDb_log.ldf'  
  9.  ,REPLACE  
  10.  ,NORECOVERY;  
  11. GO  
Step 7

Restore log backup of database, like below.
  1. RESTORE LOG TestDb_New  
  2. FROM DISK = 'D:\TestDb_log.trn'  
  3. WITH STOPBEFOREMARK = 'lsn:33000000017600001'  
  4. GO  
Step 8

Check the table data.
  1. USE TestDb_New  
  2. GO  
  3. SELECT * FROM dbo.TestTable  
  4. --Export the deleted rows from this table to original table  


Step 9

Drop tables.
  1. USE master  
  2. GO  
  3. DROP DATABASE TestDb  
  4. DROP DATABASE TestDb_New  
  5. -->The End<--  
Step for time range.
  1. SELECT [Current LSN]  
  2.  ,[Operation]  
  3.  ,[Transaction ID]  
  4.  ,[Begin Time]  
  5.  ,[Transaction Name]  
  6.  ,[Transaction SID]  
  7.  ,[AllocUnitName]  
  8. FROM fn_dblog(NULL, NULL)  
  9. WHERE --[Operation] = 'LOP_BEGIN_XACT'  
  10.  --and   
  11.  [Begin Time] BETWEEN '2015/07/17 15:30:00:000'  
  12.   AND '2015/07/17 16:00:00:000'