How to Recover Deleted Data from SQL Server Table? - 100% Working Guide

Are you searching for a solution to recover deleted data from SQL Server table? If so, then this blog is all about meeting your needs. There are many reasons for the data deletion problem. Here, we are going to discuss a 100% working and tested solution that lets you retrieve deleted records in SQL Server. We will discuss manual approaches as well as an automated solution i.e. SQL Recovery which will helps you to recover SQL Server database in an effortless manner. Let's continue with the process.

Sometimes a user can perform an UPDATE or DELETE operation on a SQL Server database without applying the WHERE condition. This is a very common reason for losing data from SQL Server tables.

Manual Approach to Recover Deleted Records in SQL Server

While performing manual methods to recover deleted table data in SQL Server, the backup (.bkf file) is very important. It is certain that if you have occasionally performed a proper backup of your SQL Server database, you will be able to retrieve deleted rows from SQL Server.

There are also requirements for your SQL backup. Because a differential backup does not always help you. To run the manual solutions, you need a full backup of your SQL Server database. Follow the steps if you have the backup.

1. Save the backup of the SQL database
2. Keep both databases individually
3. Find removed data from the backup
4. Perform the UPDATE operation on changed records

How to Recover Deleted Data from SQL Server Table Using LSN?

Deleted rows can be restored if the time of deletion is known. This can be done by using the Log Sequence Numbers (LSNs). LSN is a unique identifier for each record in the SQL Server transaction log.

To retrieve deleted rows from SQL Server using LSN, there are few prerequisites that are to be fulfilled. At the time of deletion, you must have a Full Recovery Model or a Logged Recovery Model.

Follow these steps to recover deleted records in SQL Server 2019, 2017, 2016, 2014, 2012, 2008 and other versions:

Step 1. Check the number of rows in the table from which the data was accidentally deleted.

SELECT * FROM Table_name

Step 2. Take a transaction log backup. 

USE DatabaseName

GO

BACKUP LOG [DatabaseName]

TO DISK = N’D:\DatabaseName\RDDTrLog.trn’

WITH NOFORMAT, NOINIT,

NAME = N’DatabaseName-Transaction Log Backup’,

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Step 3. Get the Transaction ID of deleted records.

USE DatabaseName

GO

Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName

FROM

fn_dblog(NULL, NULL)

WHERE Operation = ‘LOP_DELETE_ROWS’

Step 4. Now find the time when the rows (records) were deleted. The transaction ID will help you to find this information.

USE DatabaseName

GO

SELECT

[Current LSN], Operation, [Transaction_ID], [Begin Time], [Transaction_Name], [Transaction SID]

FROM

fn_dblog(NULL, NULL)

WHERE

[Transaction ID] = ‘000:000001f3′

AND

[Operation] = ‘LOP_BEGIN_XACT’

Step 5. Now start the restore process to recover deleted data from SQL Server Table, Row and Column.

Recover Deleted D USE DatabaseName

GO

RESTORE DATABASE DatabaseName_COPY FROM

DISK = ‘D:\DatabaseName\RDDFull.bak’

WITH

MOVE ‘DatabaseName’ TO ‘D:\RecoverDB\DatabaseName.mdf’,

MOVE ‘DatabaseName_log’ TO ‘D:\RecoverDB\DatabaseName_log.ldf’,

REPLACE, NORECOVERY;

GO

Step 6. In the end, check the table whether deleted rows are recovered or not.

USE DatabaseName_Copy GO Select * from Table_name

Automated Solution to Recover Deleted Data from SQL Server Table

The above mentioned manual method only works when you have the most updated backup of your database. If you don’t have updated backup to recover deleted records in SQL Server, then you need to use SysTools SQL Recovery software. With this tool, you can retrieve deleted rows from SQL Server table without any data modification. This software allows the user to recover corrupt data from MDF and NDF file with all objects like tables, views, triggers, functions, etc. It also display a preview of deleted records in red colour after recovery. Using this program, one can recover deleted table data in SQL Server and resolve all SQL errors like 5172, 5171, 823, 8946, etc.

Working of the Program to Restore Deleted Data in SQL Server

Step 1. Download and install the software then click on Open button to load .mdf file.

Step 2. Select Quick or Advanced Scan option and check the Preview Deleted Records box.

Step 3. After scanning process, you can easily see the preview of deleted records in red colour. Click on the Export button.

Step 4. Under Export Options, select export to SQL Server Database and fill the required details.

Step 5. Select Export data with Only Schema or with Schema & Data both. Now, click the Export button to start the process.

Concluding Words

The blog discussed the solution to recover deleted data from SQL Server table. If the backup of the latest database is available for recovery, you can simply use the manual approach. If you do not recover deleted table data in SQL Server by using the LSN method, try the automated solution. It will help you retrieve deleted rows from SQL Server without backup.