How to Recover Data After DELETE Command SQL Server?

  

Written By Ashwani Tiwari

Approved By Anuraag Singh

Published On May 5th, 2025

Reading Time 12 Minutes Reading

Accidental deletion can occur in any application and create huge nuisance. And, the same can occur in SQL Server database too. Therefore, we came up with this write-up. In addition, this blog will discuss about how to recover data after Deleted command in SQL Server 2017, 2016, 2014, 2012, 2008 / 2008 R2, 2005, 2000.

As we know, Transaction log of SQL Server databases comprises of number of LSN numbers for each transactions made on the respective databases. This LSN information can be utilized to recover deleted records in SQL Server with undo DELETE SQL or other ways.

What we have done here is, we created a testing database with some tables in it and tried to learn how to rollback deleted data in SQL using the LSN under which the delete statement was ran.

Situations Where Users Must Know How to Rollback Deleted Data in SQL

There are various situations where the data is deleted unintentionally or due to any other issue. In these situations, a user must know how to rollback delete query in SQL to ensure no data is lost during the action. We will now see some of these situations to understand the issue better. 

• The first cause is the Accidental Deletion of the data in the SQL Server database. This can occur due to the wrong selection of records for deletion, and further requires to recover data after DELETE Command SQL Server. 
• Another reason for the data recovery after delete command is when the users implement or run incorrect queries or logics to delete or truncate any specific data. This can result in loss of sensitive data and needs to be recovered timely. 
• When a user runs the DELETE command in the SQL Server, without taking any backups, this might result in complex or impossible recovery of the records within the database. 
• One reason for the deletion of data is Human Error. This reason might be quite common for the deletion of the records in SQL Server database. Selection of wrong records while deletion or running incorrect query are a part of this cause. 

For these reasons, it becomes necessary for the users to know the best ways to how to rollback deleted data in SQL. Here are the methods that will help the users to efficiently resolve the issues during the process and get precise results for how to rollback delete query in SQL.

Recover Data After DELETE Command in SQL Server

Let’s have a look at the three phases of this operation to get back all of the data gone with Delete command. 

Phase 1: Steps to Create A Test Database

Step 1:- Here, we have created a test database named as “ExperimentDB” with Table name “Company” having three columns. The deletion will be performed on this database table using DELETE statement.
You can Execute or Run T-SQL code written below in order to create the database and table.

Step 2:- Here we will insert some rows (25) using the below mentioned command to know how to recover deleted rows in SQL Server

Step 3:- After the successful completion of recover deleted records in SQL Server task, rows are updated successfully.

Phase 2: Steps to Delete Rows for this SQL UNDO Last Command Task

Step 1:- Now we will delete some rows which we have created earlier and recover later using LSNs available in the Transaction log. We will delete first 9 entries in the row.

The results will show that the first 9 (less than 10) entries have been deleted.

Phase 3: How to Recover Data After DELETE Query in SQL Server

Once the rows are deleted, we need to search the SQL Server Transaction Log to fetch the information about deleted rows. The code mentioned below will help you to get all the information about deleted transactions & undo a delete. This includes the solution to how to rollback deleted data in SQL.

The results expanded will be shown as below with Current LSN, AllocUnitName, Context, Operation, and most important Transaction ID.

The data to be recovered belongs to the Company table and hence we will focus on the AllocUnitName as dbo.Company. Also, the transaction ID can be easily identified to rollback deleted records in SQL which is here;

Transaction ID: 0000:0000021f

The 9 rows have been deleted under same transaction ID which indicates that the deletion has been made in batch for multiple items in one go. Carefully note this transaction ID as it is going to restore the deleted data.

• The Transaction ID will be used here in the below mentioned operation “LOP_BEGIN_XACT” to extract the LSN number allied to the DELETE transaction.

• The LSN will be extracted using the above mentioned code. Here LSN is: 00000013:0000010b:0001. This is in Hexadecimal format which has to be converted to decimal in order to restore data.

• STOPBEFOREMARK operation will be used in order to get back the data where hexadecimal value will not work. Hence this value needs to be converted to the decimal value. You can follow the below mentioned simple steps to perform this conversion.

1. LSN 00000013:0000010b:0001 can be divided into three parts; 1^st – (00000013), 2^nd– (0000010b), and 3^rd – (0001).
2. Also, one can use an online available method to convert Hexadecimal to Decimal or one can also perform the conversion manually.
3. Here 1st – (00000013) is 19, 2nd – (0000010b) is 267, and 3rd – (0001) is 1.
4. The conversion will be performed as 1^st without leading any zero, 2^nd as a 10 digit decimal number adding zeros, and 3^rd to 5 digit number with zeros.
5. In this way, the hexadecimal LSN will be converted to – 19000000026700001
6. Now run transactional log backup on the database where deletion has performed. Then we will help users learn how to rollback deleted data in SQL & restore the database to another DB (here EmployeeDBCopy) till the mentioned LSN then the data restored can be moved to production database using below mentioned command with STOPBEFOREMARK = ‘lsn: 19000000026700001’

Restoring Full Backup with NORecovery

RESTORE DATABASE ExperimentDBCopy
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ExperimentDB.bak'
WITH
MOVE 'ExperimentDB' TO 'C:\ExperimentDB.mdf',
MOVE 'ExperimentDB_log' TO 'C:\ExperimentDB_log.ldf',
REPLACE, NORECOVERY;
GO

Restore Log Backup with STOPBEFOREMARK Option to Restore Exact LSN

RESTORE LOG ExperimentDBCopy
FROMDISK = N'\C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ExperimentDB.trn'
WITH
STOPBEFOREMARK = 'Isn:19000000026700001'

• Restored data can be viewed using following command. You will be able to see the deleted rows again.

Disadvantages of Manual Solutions

There are several limitations in the manual solution for undo DELETE SQL that are going to be a challenge for users to counter. Let’s understand these drawbacks & then proceed towards an ideal & expert-recommended solution:

• Time-Consuming – The manual solution is quite lengthy as it involved a series of queries. It makes the procedure quite time-consuming & spoils the entire user experience.
• Risk of Data Loss – Data loss risk is always been there in manual solutions for how to rollback delete query in SQL. In addition, there is no surety of getting the perfect results.
• Reduce Efficiency – This method is inefficient as it wastes resources. Also, users need to compromise their like time and efforts as well that can be invested somewhere else.
• Complex Method – This solution is quite complex & difficult for new users to understand. A proficient technical knowledge is required to step in the manual solution.

Recover Data After DELETE Command SQL Server Instantly

This tool can easily fix damaged database objects like Tables, Functions, Stored Procedures, Rules, Triggers from MDF & NDF file. Besides this, it can restore deleted records from the table without any fail. Moreover, the utility recover data after DELETE Command in SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005 /2000.

Follow the step-by-step solution in depth:

Step-1. Download, Install, and Launch the software on your system to begin.

Step-2. Now, just Click on the Open button to Browse & Add MDF data files to continue recovery of SQL database data deleted by accident.

Step-3. Select the Scan Mode from the available options: Quick or Advance.

Step-4. Enter the Destination SQL Server Database to get desired results.

Step-5. At last, Click on the Export button to Recover Data After DELETE Command in SQL Server DB.

Video Tutorial to Learn Tool & Execute  SQL undo DELETE Operation

If users want they can also, take help from the visual tutorial of this automated software to experience the maximum benefit of it.

Additional Considerations to Recover Deleted Records in SQL Server

After discussing all the crucial aspects, it’s time we proceed to some key tips. This can help users to get the best results for learning how to rollback deleted data in SQL Server after DELETE command with minimal effort & almost zero errors.

• Significance of Backup: Users often forget to take a backup of the database which is why in case of a failure, they lose data. Therefore, having a data backup is crucial in such cases.
• Alternative Methods: Not all users are proficient in SQL technicalities. Therefore, it’s necessary to focus on alternative solutions so that we have multiple options available to get the solution.
• Focus on Data Safety: Data safety should also be one of the priorities for the users to keep the data integrity intact. Instead of saving money, the focus should be on data safety. 
• Limitations for Manual Recovery: Manual solutions do not always work and users can get confused for sure. Therefore, users should not rely on manual methods totally. 

Preventive Measures to Secure Data From Unintentional DELETE Command

These are a few prevention steps, that will not only help the users in keeping their data and records safe in the database, but also prevent any risk of data loss due to the DELETE command.

• The first prevention tip for users and database administrators is to implement RBAC for enhanced database security. The Role Based Access Control (RBAC) ensures that the controls to the database are given only to trusted roles and users. 
• Next, the users can secure their data by keeping a frequent backup of the SQL Database. This will help the users to recover data after DELETE Command SQL Server easily after any human errors. 
• Regular monitoring of the database will help the users to track any unauthorized operations in the database. With the right monitoring methods, users can track all the deletion operations in the SQL Server and inspect any suspicious activities in the database. 

Following all these measures will allow the users to get a clear idea for the activities in the database. Hence, it is required for the users to consider the best methods to resolve how to rollback deleted data in SQL.

Conclusion

Finally, accidental deletion of data is one of the worst cases any DBA or user faces. Therefore, we have covered the step-by-step process on how to recover data after Delete command SQL Server manually. Also, the advanced solution to rollback deleted records in SQL is also mentioned here.

Make sure to put the database in Full Recovery Mode while performing manual steps. Users looking for an instant solution to recover deleted records in SQL Server have also been discussed here.

Frequently Asked Questions

  

By Ashwani Tiwari

Being a Chief Technical Analyst, I am aware of the technicalities faced by the user while working with multiple technologies. So, through my blogs and articles, I love to help all the users who face various challenges while dealing with technology.

View all of Ashwani Tiwari's posts.