Recover Deleted Data In MSSQL.¶

To easily recover deleted rows from a table in the SQL Server database, it must have the BULK-LOGGED or FULL recovery model at the time when the deletion first occurred.
Some quick actions are required so that the logs are still available to perform the recovery of data.

• Step 1
  Check the number of rows present in the table from which the data has been accidentally deleted using the below-mentioned query:

SELECT * FROM deletedTable

• Step 2
  In order to recover deleted data from the SQL Server Table, we need to collect some information about the deleted rows. Run the query given below to achieve this purpose

USE Databasename
GO
Select [Current LSN] LSN, [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation  = 'LOP_DELETE_ROWS'

From the query given above, we will obtain the Transaction ID (Let's say 000:000001f3 ) of the deleted rows. Now, the time when these rows were deleted is to be determined using this ID.

• Step 3
  In this step, we will find the specific time at which rows were deleted using the Transaction ID 000:000001f3 . This is done by executing the query given as follows:
  

  USE Databasename
  GO
  SELECT
  [Current LSN],  Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
  FROM fn_dblog(NULL, NULL)
  WHERE [Transaction ID] = ‘000:000001f3' AND [Operation] = 'LOP_BEGIN_XACT'
  

  
  On executing this query we will get the value of the current Log Sequence Number (LSN) (let's say 00000020:000001d0:0001).

• Step 4
  Take the transaction log backup of the database using the query given below:
  

  USE Databasename
  GO
  BACKUP LOG [Databasename]
  TO DISK = N'D:\Databasename\RDDTrLog.trn'
  WITH NOFORMAT, NOINIT,
  NAME = N'Databasename-Transaction Log Backup',
  SKIP, NOREWIND, NOUNLOAD, STATS = 10
  GO
  

• Step 5
  Now we will start the restore process to recover deleted data from SQL Server Table rows that was lost. This is done using the below query:
  

  USE Databasename
  GO
  RESTORE DATABASE Databasename_COPY FROM
  DISK = 'D:\Databasename\RDDFull.bak'
  WITH
  MOVE 'Databasename' TO 'D:\RecoverDB\Databasename.mdf',
  MOVE 'Databasename_log' TO 'D:\RecoverDB\Databasename_log.ldf',
  REPLACE, NORECOVERY;
  GO
  

• Step 6
  Now apply the transaction log to restore deleted rows by using LSN 00000020:000001d0:0001:
  

  USE  Databasename
  GO
  RESTORE LOG Databasename_COPY FROM DISK = N'D:\Databasename\RDOTrLog.trn' WITH STOPBEFOREMARK = 'lsn:0x00000020:000001d0:0001'  
  --Note: Since LSN values are in Hexadecimal form and for restoring tables using this LSN, we need to convert it into decimal form. For this purpose, we add 0x just before the LSN as shown above.
  

• Step 7
  The process to recover deleted records from SQL table will get completed successfully. Now check whether the deleted records are back in the database named Databasename_Copy.
  

  USE Databasename_Copy 
  GO 
  Select * from Table_name
  
  

Query to get Step 1 result from a Backup File.¶

SELECT [Current LSN], [Operation], [Transaction Name], [Transaction ID], SUSER_SNAME([Transaction SID]) AS DBUser,
FROM fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'C:\temp\Read_Backup_Logfile_DELETEDemo_Log.bak',
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
    WHERE
        [OPERATION] LIKE ('LOP_DELETE_ROWS')