How to Recover Deleted Table Records in SQL Server

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:

• First, you will need SQL Server installed. You can use any SQL Server installed in Windows.
• Secondly, make sure you have SQL Server Management Studio (SSMS) installed
• Stellar Repair for MS SQL software

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:

• Launch Stellar Repair for MS SQL software. The software main interface opens with an instruction to stop your SQL Server database and create a copy in a different location. Press OK.

Figure 7 – Stellar Repair for MS SQL Main Interface

• Browse the SQL database MDF file you want to repair. If you do not know the location of MDF file, click Search to find and select the file:

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

• After selecting the database file, select the Include Deleted Records option, and then click Repair.

Figure 10 – Include Deleted Records Option

• Software prompts to select an appropriate scan mode to repair the database file. Select ‘Standard Scan’ or ‘Advanced Scan’, and then click OK.

Figure 11 – Select Scan Mode

• Click OK when the ‘Repair Complete’ message box opens.

Figure 12 – Repair Complete Message Box

• The software shows preview of the recoverable table records. It also restores other db objects like Views, Synonyms, Stored Procedures, Functions, Rules, Defaults, Data Types, Triggers, Sequences, and Assemblies. The software also displays a log report containing information like the database size, collation, path of the mdf file, and list of tables and objects.

Figure 13 – Log Report

• Next, click Save on the File menu to save the recovered data.

Figure 14 – Save Database

• In ‘Save Database’ dialog box, you can choose to save the recovered data in MDF, CSV, HTML or XLS (Excel) file formats. In this example, we will choose MSSQL:

Figure 15 – Saving Options

• You can create a new database or work with the database online. You can restore in another SQL Server instance and specify the Authentication type, i.e. Windows Authentication or SQL Server Authentication. Also, you can specify the location of the files of the database:

Figure 16 – Choose Authentication Type

• Click Save.

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.