How to Restore Deleted Data from SQL Server 2019, 2017, 2016?

  

Anjali Rawat   
Published: April 22nd, 2026 • 5 Minutes Reading

There are many situations where you might accidentally DROP or DELETE an entire SQL table with a misplaced WHERE clause and further lose crucial data. This blog helps to restore deleted data from SQL Server 2022, 2019, 2017, 2016, and prior versions. We will thoroughly understand how the manual and professional approaches can help the users to rollback deleted data in SQL Server.  Here is an example of  restoring deleted data from an SQL Server using the Transaction Logs.

Also Read: How to Fix DBCC CHECKDB Not Working in SQL Server?

Use Transaction Logs to Restore Deleted Data

The deleted rows in SQL Server can be recovered if the deletion time is known. We can do this using the Log Sequence Number (LSN) in SQL Server. The LSN is a unique identifier given to each record that exists in the SQL Server transaction log. Below, we will discuss the process to restore deleted data from SQL Server with the help of the transaction log and LSN.

Before starting the SQL Server deleted data recovery process, some prerequisites must be met. To easily recover deleted data items from a SQL Server database, you must have the BULK-LOGGED or FULL recovery model on first deletion. Some quick action is required so that the logs are still available for data recovery.

Follow the steps below to restore deleted data items from SQL Server 2019, 2017, 2016, 2014, 2012, 2008, and 2005 using transaction logs.

Know How to Solve SQL Database Restore Failed Issue?

Steps to Rollback Deleted Data in SQL Using LSN

Step 1: Use the query mentioned below to check the number of rows in the SQL table. The given command will help check the number of rows from where the data was deleted:

SELECT * FROM Table_name

Step 2: Get a backup of the database transaction log using the following query:

USE Database_name
GO
BACKUP LOG [Database_name]
TO DISK = N'D:\Databasename\RDDTrLog.trn'
WITH NOFORMAT, NOINIT,
NAME = N'Database_name-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Step 3: To restore deleted data from SQL Server table, we need to collect information about deleted rows. Run the query given below to achieve this

USE Database_name
GO
Select [CurrentLSN], [TransactionID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation  = 'LOP_DELETE_ROWS'

From the above query, we’ll get the transaction ID of the deleted row (for example, 000:000002r5). This ID will now be used to determine when those rows will be deleted.

Step 4: In this step, we will use the transaction ID 000:000002r5 to find the exact time of row deletion. Run the given command for the same:

USE Database_name
GO
SELECT
[CurrentLSN],  Operation, [TransactionID], [BeginTime], [TransactionName], [TransactionSID]
FROM
fn_dblog(NULL, NULL)
WHERE
[TransactionID] = ‘000:000001f3'
AND
[Operation] = 'LOP_BEGIN_XACT'

Running this query will return the current Log Sequence Number (LSN) value (for example, 00000030:000002s0:0002).

Step 5: Now we will start the recovery process to restore deleted data from SQL Server table rows. Use the following query:

USE Database_name
GO
RESTORE DATABASE Database_name_COPY FROM
DISK = 'D:\Databasename\RDDFull.bak'
WITH
MOVE 'Database_name' TO 'D:\RecoverDB\Database_name.mdf',
MOVE 'Database_name_log' TO 'D:\RecoverDB\Database_name_log.ldf',
REPLACE, NORECOVERY;
GO

Step 6: Now apply the transaction log with LSN 00000030:000002s0:0002 to restore the deleted row:

USE  Databasename
GO
RESTORE LOG Database_name_COPY FROM DISK = N'D:\Database_name\RDOTrLog.trn' WITH STOPBEFOREMARK = ‘lsn:0x00000030:000002s0:0002'

Step 7: The restore deleted data from SQL Server will complete successfully. Now check if the missing record is back in the database.

USE Databasename_Copy GO Select * from Table_name

Steps to Perform SQL Server Object Level Recovery

Limitations with Transaction Logs Steps

Transaction Logs are the first solution to perform for restoring SQL Server deleted data items. But this method also have some limitations, as mentioned below:

• The method of restoring deleted data from SQL Server tables is time-consuming because it involves several long queries to execute.
• It is extremely complex to implement for users who do not have enough technical knowledge.
• There is a higher chance of losing data due to errors in applying and executing queries.

Also Read: How to Fix SQL Backup Database is Terminating Abnormally?

Concluding Lines

Although the SQL Server Log Sequence Numbers solution can restore deleted data from SQL Server, it is not recommended for users due to its complexity. Instead, a smart solution can help restore data from SQL Server after deletion in Windows OS.