How to Recover Data After DELETE Command SQL Server?

  author
Written By Ashwani Tiwari
Anuraag Singh
Approved By Anuraag Singh
Published On April 24th, 2023
Reading Time 7 Minutes Reading

Recover Data After DELETE Command SQL Server

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. 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 for deletion recovery in SQL Server.

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

Note: It is suggested to try this recovery procedure under guidance of technical resource for Test database, before working on the production databases so as to avoid any data loss. Here we will be using function “fn_dblog” in order to find unauthorized data deletion and its recovery. Full Recovery Mode should be applied to the databases.

Undo DELETE Command in SQL – Quick Steps

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

Recover Data After DELETE command in SQL Server

Phase 1: Steps to Create A Test Database

Step 1:- Here, we have created a test database named as “ExperimentDB with Table nameCompany 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.

Run T-SQL code

 

Step 2:- Here we will insert some rows (25) using the below mentioned command.

image003

Step 3:- After the successful completion of above query, rows are updated successfully.

Phase 2: Steps to Delete Rows

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.

image005

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.

image007

 

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

image009

The data to be recovered belongs to the Company table and hence we will focus on the AllocUnitName as dbo.Company. The transaction ID can be easily identified here 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 recover 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.

image011

  • 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 recover data.

image013

  • STOPBEFOREMARK operation will be used in order to recover 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; 1st – (00000013), 2nd– (0000010b), and 3rd – (0001).
  2. 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 1st without leading any zero, 2nd as a 10 digit decimal number adding zeros, and 3rd to 5 digit number with zeros.
  5. In this way, the hexadecimal LSN will be converted to – 19000000026700001
  • Now run transactional log backup on the database where deletion has performed. Then we will restore the database to another database (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 Recover Exact LSN.

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

image016

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

image018

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 wanted.
  • 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

With the above method, you can repair the deleted data but it is a quite complex task. If you want easy and instant solution to recover deleted records from the SQL table, then try SysTools SQL Recovery Software.

Download Now Purchase Now

This tool can easily recover corrupted 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. 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.

launch tool

Step-2. Now, just Click on the Open button to Browse & Add MDF data files.

add mdf

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

select scan mode

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

enter destination server

Step-5. At last, Click on the Export button to undo DELETE in SQL Server DB.

click export

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.

Conclusion

Accidental deletion of data is one of the worst case 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.

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 has also been discussed here.

Frequently Asked Questions

Q-1. Can I recover accidentally deleted table records in SQL Server ?

Ans: Yes, it is possible to restore the deleted records of database.

Q-2. Does the automated software restore deleted records of MS SQL Server 2005 ?

Ans: Yes, the SQL Repair Tool can recover records from SQL Server 2019 / 2017 / 2016 / 2014 and below versions.

Q-3. Can I recover deleted 🗑 rows in SQL Server ?

Ans: With the Log LSN value and SQL Recovery software, you can easily restore accidentally deleted rows entry in SQL Server

  author

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.