Home » SQL » Recover Deleted Records From SQL Server Table Step By Step

Recover Deleted Records From SQL Server Table Step By Step

👤 Andrew Jackson | Published: January 23rd, 2025 | SQL | 6 Minutes Reading

The task of creating tales, storing data in log records files, etc looks quite easy in SQL Server. However, the reality is that because of external viruses, malware, ransomware attacks, & human errors, etc, data deletion is quite common. It’s not a joke to recover deleted records from SQL Server tables easily.

This article is going to be a perfect tutorial for beginners or even advanced users to easily get back deleted records from any one or multiple SQL tables. Let’s quickly get into this article without any delay.

How to Recover Deleted Table Records in SQL Server – Top 2 Methods

There are two methods that are generally popular among users the most. This article consists of both of these solutions. However, every method has its own advantages and drawbacks.

  • Manual LSN Method – Using LSNs (Log Sequence Numbers), but it works only if the time of deletion is known to the user. If the user is not aware of the data deletion time, they can’t get the actual benefits from this method.
  • Advanced Software – Simple yet secure and reliable solution for recovering deleted data from the server by using the advanced automated software as mentioned below.

Method-1. LSN to Recover Deleted Records from SQL Server Table

Deleted Records’ Recovery Using SQL Server LSN:– In SQL Server transaction logs, the LSN(Log Sequence Number) is nothing but unique identifiers assigned to each record. Here we can restore the deleted rows of SQL tables if the time when the record was deleted is known.

User has to be ensured that the Full Recovery Model or Logged Recovery Model were created when the data was actually deleted for starting the recovery process. This is the prerequisite for the successful recovery of the deleted records.

The steps are described below to recover the deleted data from SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 R2, 2008 and 2005.

Step-1. Creating Database

The very first step here is to create a database using SQL commands. Users can copy the command mentioned below. Here, we are creating the DB with the same Test_Recovery and a table with Users with three columns.

USE [master];  
GO   
CREATE DATABASE Test_Recovery ;
GO
USE Test_Table ;   
GO
CREATE TABLE [Users] (
[Sr.No] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT 'City1');

Users must pay attention that here we combined three two into one query. Thats it. Once the database is created, we can proceed to the next step.

Step-2. Adding Table Data

Once the DB and table are created, it’s time for users to add data to the tables. Follow the below query.

USE Test_Recovery;
GO
INSERT INTO Employee DEFAULT VALUES;
GO 100

Step-3. Deleting Rows from the Table

Copy and run the following command in your database to delete the rows consisting of data as per your preferences.

USE Test_Recovery
Go
DELETE Users
WHERE [Sr.No] < 10
GO
Select * from Users

After executing this command, users can experience the rows with Sr.No value less than 10 are deleted.

Step-4. Get the Deleted Records Information

Now, simply search for the transaction log records to find out the LSN.

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

Getting the transaction ID of the deleted rows is not enough. Users will have to simply find the time as well

Step-5. Getting the LSN (Log Sequence Number)

Now, users need to get the LSN number of the LOP_BEGIN_XACT option.

USE Test_Recovery
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'

Step-6. Recover Deleted Records from SQL Server Table

Now, that users have LSN, they can simply proceed to learn how to recover deleted table records in SQLServer using LSN. Copy the command below to recover deleted data.

--Restoring Full backup with norecovery.

RESTORE DATABASE Test_Recovery_Copy
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SG\MSSQL\Backup\Test_Recovery.bak'
WITH
    MOVE 'Test_Recovery' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SG\MSSQL\Backup\Test_Recovery.mdf',
    MOVE 'Test_Recovery_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SG\MSSQL\Backup\Test_Recovery.ldf',
    REPLACE, NORECOVERY;
    GO

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

   RESTORE LOG Test_Recovery_COPY
FROM
    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SG\MSSQL\Backup\Test_Recovery_tlogbackup.trn'
WITH
    STOPBEFOREMARK = 'lsn:0x00000014:0000001a:0001'

That’s it. Now, users have recovered the table records from SQL Server table using the LSN method. However, as we can see that this method is quite long and confusing, let’s proceed towards the automated solution.

Method-2. Recover Deleted Records From SQL Server 2022, 2019, & All Others

If you failed to recover deleted data from SQL server table by transaction logs then you can take the help SQL Database Recovery Program. This software provides you the option to recover deleted records from SQL server table. Also by using this software, the user can preview accidentally deleted SQL tables records in red color. The user can easily recover database objects such as tables, functions, stored procedure. Moreover, This application is compatible with SQL server 2017 and its below version.

Download Now Purchase Now

Follow The Steps to Recover Deleted Records From SQL Server Table

  1. Download and Install the software on your machine.
  2. Click on Add file button and add the MDF file in the software.
  3. Now choose the Scan option and select the SQL server version.
  4. Check the option preview deleted SQL database records in red color.
  5. Preview the SQL server database items. The software will preview the deleted SQL table records in red color.
  6. And click on Export button to Export the SQL database.
  7. Now in database authentication choose the server name and the authentication mode.
  8. Now choose the destination database.
  9. Check the database items you want to export.
  10. Choose the option from with only schema and schema and data.
  11. Mark the option Export deleted records and click on Export button.

Final Words

In this article, we have discussed how to recover deleted data from SQL server table by transaction logs. The manual solution is quite lengthy and difficult to perform. It requires strong technical knowledge. So it is better to take the help of expert solution to recover deleted records easily.