How to Recover Deleted SQL Server Rows [2024]

Tracy King updated on Jan 09, 2024 to MS SQL Database Recovery | How-to Articles

When a single or several rows gets deleted in SQL Server, you may not be able to visit a page, a website or request to check content of your business. Relax! EaseUS software with the best SQL Recovery tool can help you effectively restore deleted rows and repair SQL Server back to work.

Applicable to MS SQL Server 2017, 2016, 2014, 2012, 2008, etc.

How to restore deleted rows in SQL Server

How do I restore a deleted row or record in SQL Server

Have you ever met this problem while using SQL Server to manage your business or provide service to your clients? Deleting rows, tables, page,s or columns in SQL Server database is a common issue that most administrators may have or will have to face and figure out solutions to this problem.

Mostly, when a database or database components get deleted, MS SQL Server administrators will try to restore them from backups. But when you deleted database or tables without backups in SQL, what will you do? EaseUS software has collected two solutions here to help you restore a single or multiple rows, tables, pages or columns in SQL by using an automated SQL Recovery tool or manual method with SLN. 

Follow to see how to bring the missing data back and make everything in your business run back on track.

Recover deleted rows in SQL Server with SQL Recovery software [Easy]

An efficient way of bringing the lost SQL rows back is to apply an automated SQL recovery tool for help. Indeed, powerful SQL recovery software can save your time and energy for restoring SQL Server data.

When you search online, remember to pick 100% secure software provided by experienced companies. EaseUS, with over a decade of data recovery experience, recently bring out its SQL recovery software with professional features to help you resolve SQL Server issues. 

With it, you can effortlessly restore deleted records, including undelete data in SQL, repair corrupted databases, MDF file restore, etc. With only a few simple clicks, you’ll bring the lost SQL Server rows back:

Step 1. Download and run EaseUS MS SQL Recovery.

Step 2. Click the two dots (Browse button) to choose the target MDF file or click "Search" and select the desired file to repair from the list found below. Then, click "Repair" to start the repairing process. 

Step 3. When the process completes, a Window will appear to confirm the successful analysis. All the deleted tables & records recovered items will be listed on the left pane in the name of the original tables.

Step 4. Click the "Export" button in the bottom right corner of the screen. You can save the recovered database to database & SQL scripts based on your need. Then, you need to enter Server/Instance Name and connect to the server. If you choose to "Export to database", you can create a new database or export to an existing database. 

After saving the deleted records including your lost rows as an MDF file, you can import or save them back to the original location of your SQL Server database.
Then restart and run SQL Server again.

Manually Recover SQL Server rows with LSN [Complex]

Another more complex method that you can try is to use LSN (Log Sequence Numbers) to check and restore the deleted rows.

Note: Be careful while you are following the below steps for deleted rows recovery in SQL.

Step 1. Check the numbers of present rows except for the deleted ones in the SQL table

SELECT * FROM Table_name

Step 2. Use the below query to bring the log back:

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. Gather information on deleted records from the SQL Server table with the below query:

USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = ‘LOP_DELETE_ROWS’

By doing so, get the Transaction ID of deleted records.

Step 4. Find the exact time when the records get deleted with the Transaction ID:

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. Recover deleted data including rows from the SQL Server table:

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. Check if deleted records including rows are recovered back to the SQL Table database:

USE Databasename_Copy GO Select * from Table_name

Tip: Back Up MS SQL Server Database Regularly

Besides knowing the way to restore lost records or rows in the SQL Server table database is not enough.

It’s also important to back up the SQL database regularly. Automated SQL Server backup software is absolutely the best choice for you to create a full backup of your huge SQL Server database. If you are looking for a smart way to protect your SQL database, EaseUS Todo Backup Advanced Server is a second-to-none choice that you can’t miss.