How to identify when data is deleted from the table and how to recover deleted record form table in SQL Server?

If you are DBA or want to become DBA then you must know this solution.

Scenario: - Someone has deleted the few record form the table from production. Now client is asking to recover that data from database backup file. DBA have to find out when and who deleted the data.

Below are the simple steps to recover the data-

1.       Created database <i.e. Recover_Data_DB >-

USE [master]

GO

CREATE DATABASE [Recover_Data_DB]

 CONTAINMENT = NONE

 ON  PRIMARY

( NAME = N'Recever_Data_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL_INSTANCE3\MSSQL\DATA\Recever_Data_DB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'Recever_Data_DB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL_INSTANCE3\MSSQL\DATA\Recever_Data_DB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

2.       Create Table <i.e. Table_1>

USE [Recover_Data_DB]

GO

  

CREATE TABLE [dbo].[Table_1](  [Name] [varchar](50) NULL) 

  

GO

  

3.       Insert value and verify the record.

SELECT  *  FROM [Recover_Data_DB].[dbo].[Table_1]

4.       After Inserting record take Full back up and Take T. Log backup.

5.       Now delete the record from the table

Delete [Recover_Data_DB].[dbo].[Table_1] where name like '%Verma%'

  

6.       Using below query can find out the deleted transaction log ID <i.e. 0000:00000313>  Here you will know all deleted transaction. 

Use [Recover_Data_DB]

SELECT

 [Current LSN],   

 [Transaction ID],

     Operation,

     Context,

     AllocUnitName

   

FROM

    fn_dblog(NULL, NULL)

WHERE

    Operation = 'LOP_DELETE_ROWS'

7.       Now using below query we can know the beginning  for the delete transaction. Just use the transaction ID (above query will give you the transaction ID <'0000:00000313'>). 

Use [Recover_Data_DB]

go

SELECT

 [Current LSN],   

 Operation,

     [Transaction ID],

     [Begin Time],

     [Transaction Name],

     [Transaction SID]

FROM

    fn_dblog(NULL, NULL)

WHERE

    [Transaction ID] = '0000:00000313'

AND

    [Operation] = 'LOP_BEGIN_XACT'

8.       Now you will get the below result (LSN number, delete time etc..)

8.1 Who deleted the data(UserName). In 8th step you will get Transaction SID. Run the below query with Transaction SID to know who delete the data.

USE MASTER

GO  

SELECT SUSER_SNAME(0x01050000000000051500000010290E9237E7FD9BEAF0DB39F4010000)

9.   Now, you have to convert the above got Current LSN Number <i.e 00000023:000000c8:0002> (it is in hexadecimal format) in Decimal format.

Divide this value in 3 parts from “:” -

Hexadecimal value - 00000023:000000c8:0002

Hexadecimal value	Converted Decimal Value	Have to Add Zero	Converted value	Now Merge converted value
00000023	35		35	35000000020000002
00000090	200	7 zero	0000000144
0002	2	4 Zero	00001

10.   Now restore database in test Server or change the name of DB while restoring it, Use below query-

Full Back-up Restore with No recovery option

USE [master]

RESTORE DATABASE [Recover_Data_DB_Copy]

FROM  DISK = N'D:\Recover_Data_DB_Full.bak' WITH  FILE = 1,

MOVE N'Recever_Data_DB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL_INSTANCE3\MSSQL\DATA\Recever_Data_DB_Copy.mdf',

MOVE N'Recever_Data_DB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL_INSTANCE3\MSSQL\DATA\Recever_Data_DB_log_copy.ldf',

NORECOVERY,  NOUNLOAD,  STATS = 5

GO

T Log back restore with STOPBEFOREMARK option

RESTORE LOG [Recover_Data_DB_Copy] FROM

DISK = N'D:\Recover_Data_DB_TLog.trn' WITH  FILE = 1,

STOPBEFOREMARK = 'lsn:35000000020000002'

GO

11.   Change the database from No Recovery mode to Recover mode

RESTORE DATABASE [RECOVER_DATA_DB_COPY] WITH  RECOVERY

12.   Verify recovered data

SELECT * FROM [Recover_Data_DB].[dbo].[Table_1]

Wow - We recovered the deleted data.

Please let me know if you know any other method to do recover the deleted record from table Jainendra Verma