How to Recover Data After DELETE Command SQL Server?

  

Written By Ashwani Tiwari

Approved By Anuraag Singh

Published On November 15th, 2024

Reading Time 10 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 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.

Undo DELETE Command in SQL – Quick Steps

1. Launch the Automated Utility on System.
2. Click the Open Button to Add MDF  Files.
3. Select Quick or Advance Scan Options Here.
4. Now, Enter the Destination Server Location.
5. Hit the Export Button to Get desired Results. 

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 SQL recover deleted rows recovery.

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 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 SQL undo DELETE. 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 in 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 undo DELETE 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 in SQL Server after DELETE 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. 

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.