How to Recover Deleted Records in SQL Server?

Pooja Chaudhary
Pooja Chaudhary linkedin | Updated On - April 21, 2023 |

Read time 6 minutes

There are times when users face disastrous database situations in their SQL Server databases. The scenarios could take place during the management and maintenance of their database records. Sometimes the database records are deleted by mistake, due to which database administrators face many issues. Hence, to get back to the normal workflow, the records need to be recovered back to the SQL Server database.

Here are some suggested methods that you can use to recover deleted records in SQL Server database.

Methods to Recover Deleted Records in SQL Server

In this section, we will discuss manual as well as an automated method to recover deleted data from SQL Server.

Instant Solution

Try Kernel SQL Database Recovery software to recover deleted records from SQL server database MDF/NDF files.

Recover Deleted Data in SQL Server Using LSN:

LSNs (Log Sequence Numbers) are unique identifiers that are assigned to every record in the SQL Server transaction logs. Hence, deleted rows of SQL tables are recoverable if the time of their deletion is known.

To start the recovery process there are several prerequisites to be fulfilled to recover deleted data from the SQL Server Table using LSN (Log Sequence Number). For smooth recovery of deleted rows from SQL Server database table, it should have Full Recovery Model or Logged Recovery Model at the time the data deletion took place.

Use the below-mentioned steps for recovery of deleted data from SQL Server 2016, 2015, 2014, 2012, 2008 and 2005.

Step 1: Use the below-mentioned query to check the number of rows present in the table from which the data got deleted.

SELECT * FROM Table_name

Step 2: Next, consider taking log back with the help of the mentioned query below:

USE Databasename
BACKUP LOG [Databasename]
TO DISK = N’D:\Databasename\RDDTrLog.trn’
NAME = N’Databasename-Transaction Log Backup’,

Step 3: Gather information about the deleted records from the SQL Server table to recover data.

USE Databasename
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
fn_dblog(NULL, NULL)

With the help of this query, you will obtain Transaction ID of deleted records.

Step 4: You can execute to the query to find specific time at which the records got deleted using the Transaction ID.

USE Databasename
[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
fn_dblog(NULL, NULL)
[Transaction ID] = ‘000:000001f3′
[Operation] = ‘LOP_BEGIN_XACT’

You get the ongoing LSN with the help of above query.

Step 5: Now, recover the deleted data from the SQL Server Table by starting the restore process.

Recover Deleted D USE Databasename
DISK = ‘D:\Databasename\RDDFull.bak’
MOVE ‘Databasename’ TO ‘D:\RecoverDB\Databasename.mdf’,
MOVE ‘Databasename_log’ TO ‘D:\RecoverDB\Databasename_log.ldf’,

Step 6: Lastly, check whether deleted records are recovered back to the SQL Table database.

USE Databasename_Copy GO Select * from Table_name

Using Automated Solution:

The third-party automated software Kernel for SQL Database Recovery is the most trusted and recommended solution for efficiently recovering deleted records of SQL Server database files. The SQL server recovery software allows to recover data from healthy as well as corrupt MDF/NDF files of SQL Database. The tool restores all database objects like tables, triggers, functions, deleted records, rules, etc.

Follow the mentioned steps for flawless recovery using this software.
Step 1: Launch the software and click Open to load MDF/NDF database file.

load MDF/NDF database file

Step 2: Click Browse to select MDF/NDF database files and select the SQL version if known or set it to autodetect and click Recover button.

select the SQL version

Step 3: The tool scans and shows the full Preview of recovered database records which were deleted.

recovered database records

Step 4: You can save the data to Live SQL Server database by simply typing the login credentials.

save the data to Live SQL Server database

Step 5: If you do not want to save to the current Live SQL Server, select Batch-File and select a location on your system to save the recovered data. This file can be accessed anytime whenever desired.

save the recovered data

Step 6: The data is saved successfully to the desired location as shown.

saved successfully to the desired location


We suggest you perform the manual method of recovery under expert guidance as the method is very tedious and takes a long time in the processing. Also, there are much chances that the data will not recover as the process does not guarantee about data recovery. Kernel SQL Data recovery is the best tool to recover corrupt or deleted database files. It performs the perfect recovery in every situation and brings your data back.

Video – How to Recover Deleted Records in SQL Server?


Kernel for SQL Database Recovery