How to Recover Deleted Table Records in SQL Server

Priyanka Chauhan | Updated on December 7, 2023 SQL Database Repair

Summary: This blog explains how to recover deleted table records in SQL Server. It demonstrates an example to retrieve deleted records from a SQL table using a Log Sequence Number (LSN). It also explores Stellar Repair for MS SQL software to recover deleted records quickly without any data loss.

If you?ve accidentally executed a DROP or DELETE command against a table with a wrong WHERE clause and lost important records, you can recover them by using any of the following methods.

Note: The truncate operations cannot be recovered. Only the delete operations can be recovered.

Methods to Recover Deleted Table Records in SQL Server

Method 1 ? Using Log Sequence Number (LSN)

Note: This method won?t work if the transaction log backup is corrupted or missing. In that case, skip to the next method.

Each SQL Server transaction log record is uniquely identified by a log sequence number (LSN). You can construct restore sequences by using the LSN of a log record at which a significant event occurred.

Let?s take an example to understand how you can use the log sequence number method to recover deleted table records. In this example ? we?ll create a database and a table, insert rows into the table, delete some rows, get information about deleted data, and recover the data.

Steps to Recover Deleted Records from SQL Table with LSN

In this section, we will create a test database and a table on which we will run a DELETE operation. Then, we will find deleted data and recover it using LSN.

Step 1: Create a Database

Execute the following query to create a database named ?RecoverDeletedRecords? and a table named ?Employee?:

USE [master];  

GO   

CREATE DATABASE RecoverDeletedRecords;

GO

USE RecoverDeletedRecords;   

GO

CREATE TABLE [Employee] (

[Sr.No] INT IDENTITY,

[Date] DATETIME DEFAULT GETDATE (),

[City] CHAR (25) DEFAULT 'City1');
Figure 1 – Create Database in SSMS

Step 2: Insert Data into Table

We have created a table named ?RecoverDeletedRecords? and a table ?Employee? with three columns. Now, we?ll insert rows into the table by running the following query:

USE RecoverDeletedRecords;

GO

INSERT INTO Employee DEFAULT VALUES;

GO 100
Figure 2 – Insert Records into New Table

Step 3: Delete Rows from Table

Now let?s delete some rows by executing the following:

USE RecoverDeletedRecords

Go

DELETE Employee

WHERE [Sr.No] < 10

GO

Select * from Employee
Figure 3 – Delete Table Rows

As shown in the image above, all the rows having Sr.No less than 10 have been deleted from the table ?Employee?.

Step 4: Get Information about Deleted Rows

Next, we will get information about the deleted rows by searching the transaction log:

USE RecoverDeletedRecords

GO

SELECT

 [Current LSN],   

 [Transaction ID],

     Operation,

     Context,

     AllocUnitName

FROM

    fn_dblog(NULL, NULL)

WHERE

    Operation = 'LOP_DELETE_ROWS'
Figure 4 – Transaction ID of Deleted Rows

After getting the Transaction IDs of the deleted rows, we?ll have to find the time when the rows were deleted.

Step 5: Get Log Sequence Number of the LOP_BEGIN_XACT Log Record

To find the exact time when the rows were deleted, we must use the transaction ID to get the LSN of the LOP_BEGIN_XACT log record of a transaction:

USE RecoverDeletedRecords

GO

SELECT

 [Current LSN],   

 Operation,

     [Transaction ID],

     [Begin Time],

     [Transaction Name],

     [Transaction SID]

FROM

    fn_dblog(NULL, NULL)

WHERE

    [Transaction ID] = '0000:0000020e'

AND

    [Operation] = 'LOP_BEGIN_XACT'
Figure 5 – Current LSN of Transaction Log

In the above screenshot, we can see the current LSN of the transaction along with details like the time ?2021/03/15 19:36:59:337? when a DELETE statement was executed with LSN ?00000014:0000001a:0001? under transaction ID ?0000:0000020e?. To recover the deleted SQL database table data, proceed with the next step.

Step 6: Recover Deleted Records in SQL Server

To recover the deleted SQL table records, we will need to convert LSN values from hexadecimal to decimal form.  To do so, add ?0x? before the log sequence number as shown in the code below:

--Restoring Full backup with norecovery.

RESTORE DATABASE RecoverDeletedRecords_COPY

    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak'

WITH

    MOVE 'RecoverDeletedRecords' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.mdf',

    MOVE 'RecoverDeletedRecords_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.ldf',

    REPLACE, NORECOVERY;

    GO

--Restore Log backup with STOPBEFOREMARK option to recover exact LSN.

   RESTORE LOG RecoverDeletedRecords_COPY

FROM

    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords_tlogbackup.trn'

WITH

    STOPBEFOREMARK = 'lsn:0x00000014:0000001a:0001'
Figure 6 – Recover Deleted Records

Now that your data is restored, check whether the deleted records have been recovered. For this, run the following query to see the first ten rows of the table that were deleted (in Step 3 above).

USE RecoverDeletedRecords_COPY

GO

SELECT * from Employee

If the deleted table records have not been recovered, try using a SQL recovery tool such as Stellar Repair for MS SQL to get back the deleted data.

Note: In the code above, replace the path ?C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak’ with the path where your backup file is located. Also, replace the path of .mdf and .ldf files with the path where you have saved the database files.

Method 2 ? Using SQL Recovery Software

The SQL recovery software from Stellar® can help you recover all the deleted records in a SQL Server database in a few simple steps. Besides the deleted data, the software can also recover all other objects like views, triggers, stored procedures, etc. while maintaining data integrity.

Before We Proceed

Before using the software to restore the records, make sure your system meets the following prerequisites:

Steps to Recover Deleted Table Records using Stellar Repair for MS SQL

Download the demo version of the SQL recovery software to recover deleted rows or damaged databases:

Figure 7 – Stellar Repair for MS SQL Main Interface
Figure 8 – Select Database MDF File in Software

Note: If you do not know where your database is located, you can find it in SSMS in database properties, in the data file page (refer to the image below).

Figure 9 – Check database Location in SSMS
Figure 10 – Include Deleted Records Option
Figure 11 – Select Scan Mode
Figure 12 – Repair Complete Message Box
Figure 13 – Log Report
Figure 14 – Save Database
Figure 15 – Saving Options
Figure 16 – Choose Authentication Type

The recovered table records and other selected data will be saved in the repaired MDF file.

Watch the complete working process of the SQL Recovery software:

Conclusion

In this article, we learned how to recover deleted records in SQL Server. These solutions can work in any SQL Server version in Windows. If you?ve the Full database backup available, you can try recovering deleted records using the transaction log with Log Sequence Number (LSN). If the backup is corrupted, use Stellar Repair for MS SQL to recover the deleted data in SQL Server or in other formats like Comma Separated Value (CSV) files, HTML, or XLS.

The SQL database repair software not only helps in recovering deleted rows but also restores the SQL database damaged by virus attack, hardware failure, or other reasons.

Related Post

Repair Database Corruption: GAM, SGAM, and IAM in SQL Server

Bharat Bhushan April 17, 2024

How to Protect MS SQL Servers from RE#TURGENCE Campaign and Other Attacks?

Bharat Bhushan January 23, 2024

How to Fix SQL Error “The Backup Set Holds a Backup of a Database other than the Existing Database”?

Monika Dadool January 15, 2024