If you ever have the misfortune of having data deleted from the production database, don’t get upset as there is a way you can recover most of the missing data with the aid of Transaction Logs and LSNs (Log Sequence Numbers).

Here is a step-by-step solution on how to achieve this.

Even if you use SqlBak to backup everything unauthorized data deletion from your database is one of the worst nightmares in terms of database administration. If you know when your data was deleted then the challenge to recover the deleted data is not as difficult. But, if you do not know the date and time when the data was deleted, then we will have to find a different way of identifying the data.

How to recover deleted data in SQL Server

Here are the steps we are going to use in recovering our data.

• For testing and example purposes we will create a database and a table schema.

• USE [master];
  GO
  CREATE DATABASE RecoverTestDatabase
  GO
  
  USE [RecoverTestDatabase];
  GO
  CREATE TABLE [BlogArticles](
      [Article_ID] INTEGER IDENTITY,
      [Artilce_Date] DATETIME DEFAULT GETDATE(),
      [Article_Creator] NVARCHAR(50) DEFAULT 'Admin');

• Populate the table with data using this script.

• INSERT INTO BlogArticles DEFAULT VALUES;
  GO 30

• Continue by deleting some records from the table in order to recover them through the help of LSNs in the SQL Server Transaction Log.

• DELETE FROM BlogArticles
  WHERE Article_ID < 15;
  GO
  
  SELECT * FROM BlogArticles;

  You can see the results returned by the query above that the records are actually missing from the table.

• Now, let’s go and review the Transaction Log of SQL Server to get the deleted records. Run the next query in order to get basic information about the deleted records.

  SELECT
      [Current LSN],
      [Transaction ID],
      [Operation],
      [Context],
      [AllocUnitName]
  FROM fn_dbLog(NULL, NULL)
  WHERE [Operation] = 'LOP_DELETE_ROWS'

  If you’ve gone along using all of the queries in this example, then after running the query above you should have the following result set returned.
  

• The Transaction ID column will show us what DELETE statement was run, but in case you didn’t notice the AllocUnitName column stores the table from which the transactions have been deleted. You can additionally filter the results to display only those of this table with the query below.

• SELECT
      [Current LSN],
      [Transaction ID],
      [Operation],
      [Context],
      [AllocUnitName]
  FROM fn_dblog(NULL, NULL)
  WHERE [Operation] = 'LOP_DELETE_ROWS'
      AND [AllocUnitName] = 'dbo.BlogArticles'

• Other columns give us information about the table type, in this case, it’s a HEAP type of table and the transaction that the DELETE statement was performed through transaction 0000:0000022c, and as we can see there are multiple records with the same transaction ID.This type of information is quite important as it shows us that all of the records from the BlogArticles table were deleted with one transaction, which doesn’t necessarily mean one query. In order to prove this run the next batch of queries:

  BEGIN TRANSACTION
  
  DELETE FROM BlogArticles
  WHERE Article_ID % 2 = 0
  
  DELETE FROM BlogArticles
  WHERE Article_ID % 3 = 0
  
  COMMIT

• If now we are to go and run the query that displays data from fn_dblog, we will see that both queries have run in the same transaction, thus proving that this is not sufficient evidence to identify the number of queries.
  
• Now, we are going to take the transaction ID from the queries above. We will continue with the transaction ID returned by our second query, the one in the image above highlighted with blue, 0000:00000248.Please be aware that from this point on you will have to use the LSN you have in your result set. We will continue this example with the LSN we were provided with by the query.
• Using this transaction ID we will use it in the next statement to retrieve the LSN (Log Sequence Number) of the LOP_BEGIN_XACT operation. This means we will need the exact LSN when the DELETE query began its transaction.

• SELECT
      [Current LSN],
      [Operation],
      [Transaction ID],
      [Begin Time],
      [Transaction Name],
      [Transaction SID]
  FROM fn_dblog(NULL, NULL)
  WHERE [Transaction ID] = '0000:00000248'
      AND [Operation] = 'LOP_BEGIN_XACT'

• The above query should give you similar output to the one in the image below.
  
• Now we know the time the DELETE statement was executed, what is its LSN and what transaction it was executed under.
• The next step involves changing the hexadecimal value of the LSN number into a decimal so we will be able to retrieve our data.
• Run the below query once for each part of the LSN and specify that part as a parameter to get the decimal number. In our LSN’s case the queries that we need to run are:

• SELECT CONVERT(INT, CONVERT(VARBINARY, '0x00000014', 1))
  SELECT CONVERT(INT, CONVERT(VARBINARY, '0x000000ee', 1))
  SELECT CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1))

  

• Now that we have the decimal values of the sections of the LSN we will be using the STOPBEFOREMARK operation in order to recover our data. We will use the decimal returned by the first query as it is, in this case, it’s 20. But the next decimals have to be preceded by leading zeros. The second decimal has to be preceded by zeros until it has a length of 10 characters, while the third should also be preceded by zeros until it has a length of 5. Thus, the outputs that you are going to need in use are returned by the following queries:

  -- Outputs the decimal for the second section
  
  SELECT RIGHT('0000000000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x000000ee', 1)) AS NVARCHAR), 10)
  
  -- Outputs the decimal for the third section
  
  SELECT RIGHT('00000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) AS NVARCHAR), 5)

  Now that we have the three decimal parts we can put the numbers together in order to get the LSN number. In our case, the LSN is 20000000023800001.

• The next step is to run a Transaction Log Backup on the database from where data was deleted if there was no previous transaction log backup done since the data was removed.
• Now, we will do a restore of this database at a different location or on the same server with a different name up until the point of the LSN number we calculated above.
• After that, we will import the missing data from the newly restored database to the main database

  -- Starting first with restoring the FULL BACKUP with NORECOVERY
  RESTORE DATABSE RecoverTestDatabase_COPY
      FROM DISK = 'C:\RecoverTestDatabase_FULL.bak'
  WITH
      MOVE 'RecoverTestDatabase' TO 'C:\RecoverTestDatabase.mdf'
     ,MOVE 'RecoverTestDatabase_log' TO 'C:\RecoverTestDatabase_log.mdf'
     ,REPLACE
     ,NORECOVERY;
  GO
  
  -- Restore LOG backup with STOBEFOREMARK option to recover exact LSN
  RESTORE LOG RecoverTestDatabase_COPY
      FROM DISK = 'C:\RecoverTestDatabase_log.trn'
  WITH
      STOPBEFOREMARK = 'lsn:20000000023800001'

• At this point your restored database is ready and you can check if the deleted data is recovered or not. Run the below SELECT to see the first rows which were deleted.

• USE RecoverTestDatabase_COPY
  
  SELECT * FROM BlogArticles

  

• You can see in the above screenshot that all rows were recovered. Now, this data can be used. You can move the data from this database restore to your main database through SSIS‘s Import and Export feature.