SQL Undo DELETE Command – Best How to Tutorial

Published By Aswin Vijayan

Approved By Anuraag Singh

Published On October 4th, 2023

Reading Time 10 Minutes Reading

Category SQL Server

Accidental deletion of data files in any application is quite common among users. We all must have faced this problem in our life. Similarly, when this happens in SQL database, SQL undo DELETE is the command we have to fix the issues. Of course, there are other ways possible for users to get the deleted files back.

With the mentioned solutions, users can easily recover deleted data in SQL 2022, 2019, 2017, 2016, 2014, 2012, 2008/R2, 2005, & 2000 versions. Also, to be more specific, we know that the transaction log of the SQL Server stores the LSN numbers for each & every transaction made. Furthermore, this LSN number can be utilized for the recovery of deleted files in the database later.

Agenda: In this article, we are going to create a test database & then delete an object. Also, using the available methods, users can learn how to undo DELETE SQL command easily.

How to Undo DELETE in SQL Server? Starting from Scratch

Note: We recommend users perform this demonstration under the surveillance of a technical resource. Here we are going to perform several functions & commands like fn_dblog, Full Recovery Mode, etc.

We are dividing this task into three Stages. Stage one includes database creation, followed by deletion of rows in stage two, followed by recovery of deleted items in stage three.

Stage-1. Create A Database in SQL Server Step by Step

For example,  we are going to create a database with the name “ExperimentDB” & the table name “Company” with at least three columns in it.

Now, first, we are going to create the database using the respective commands and then we will add a few rows in them to proceed further. 

Step-1. Creating a database with:

• Name: ExperimentDB
• Table: Company
• Columns: 
• Sr. No
• Date
• City

-- Create the database

CREATE DATABASE ExperimentDB;
Go

-- Switch to the new database & Create tables

USE ExperimentDB;
Go
CREATE TABLE [Company] (
    [Sr. No] INT IDENTITY,
    [Date] DATETIME DEFAULT GETDATE (),
    [City] CHAR (20) DEFAULT ‘xyz’ ) ;

Step-2. Adding Rows to the table we just created. Approximately 25 rows.

USE ExperimentDB;
Go
INSERT INTO COMPANY DEFAULT VALUES ;
GO 25

That’s it,  we have successfully completed the first stage, Now, let’s proceed towards the second stage for SQL undo DELETE command.

Stage-2. Deleting Rows from ExperimentDB Database

Now, it’s time we delete some of the rows we created above to make the situation of deleting files accidentally. For this, let’s delete approximately 9 rows to proceed with.

Command:

USE ExperimentDB
Go
DELETE Company
WHERE [Sr. No] < 10
Go
SELECT * from Company

Now, if you have reached here, well done!! Now, we are moving ahead to stage three. This involves recovering the files using undo DELETE SQL script or the automated ways.

Undo DELETE Command in SQL Database with Stage 3

After the rows are deleted, what we need to do is nothing but recover them. In order to get the information about the deleted, users need to find out their transaction logs. Also, follow the below-mentioned command to get the desired solution.

USE ExperimentDB
Go
SELECT
[Current LSN]
[Transaction ID] ,
Operation,
Context,
AllocUnitName

FROM
Fn_blog (NULL, NULL)
WHERE
OPERATION = ‘LOP_DELETE_Rows’

Just after running the command, users will get to see the results as shown in the below image. Here, they can see the existing  AllocUnitName, Context, Operation, & of course the transaction ID.

In order to execute SQL undo DELETE command, as we know that data is totally related to the Company table. Therefore, we are going to focus on AllocUnitName as dbo.Company.

Also, we can see that the trans ID here is: 0000:0000021f without a doubt.

We have only one transaction ID which means that 9 rows deleted are a part of a bulk deletion process. Now, this ID is going to play a major role to Undo DELETE command in SQL database.

This transaction ID is required to find out the LSN number of the deleted rows. For this, users need to execute the below command:

USE ExperimentDB
Go
SELECT
[Current LSN] ,
Operation,
[Transaction ID]
[Begin Time]
[Transaction Name]
[Transaction SID]

FROM
Fn_dblog (NULL, NULL)
WHERE
[Transaction ID]  = ‘0000:0000021f’
AND
[Operation] = ‘LOP_BEGIN_XACT’

Here, we can say see that 00000013:0000010b:0001 is the LSN number. Now, users must be aware of the fact that this is a hexadecimal format but what we need is a decimal format. Therefore, we need to convert it.

Now, we are going to use the “STOPBEFOREMARK” for the data recovery task as the hexadecimal value is of no use to us in this case. Follow the below steps for this conversion & executing the Undo DELETE command in SQL DB.

• • Putting it simply, we can say that the LSN number 00000013:0000010b:0001 can be further divided into three parts:
    • 1st: 00000013
    • 2nd: 0000010b
    • 3rd: 0001
  • There are both online as well as manual ways available for this conversion task.
  • Furthermore, users can proceed as:
    • 00000013 is 19
    • 0000010b is 267
    • 0001 is 1

• The conversion will be executed in such a manner:
  • 1st – Without Any Zeros
  • 2nd – 10 Digit Decimal Number with Zeros
  • 3rd – 5 Digit Number with Zeros

Therefore, the 10 Digit Hexadecimal number will be converted easily. Hence, the final result can be seen as 19000000026700001 simply.

Now, run the transaction log backup for the database where deletion took place. After that, users can restore the data to another database say EmployeeDBCopy till the mentioned LSN.

Later users can easily move this data to the production database taking help of the below command: 

STOPBEFOREMARK = ‘lsn: 19000000026700001

Data Restoring Options in Command Line

Restoring Full Backup with NoRecovery is the first command that we have. Let’s look at this in depth.

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 the deleted database files using the STOPBEFOREMARK option. Now, users might be feeling that learning how to undo DELETE in SQL database is tricky with this manual method. 

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

Now, execute the following command to view the data we just restored.

USE ExperimentDBCopy
Go
SELECT from Company

Drawbacks of SQL Undo DELETE Command

Now, we must discuss the drawbacks of the manual command first. For that, we can say that there are not a few but plenty of disadvantages for users to use this solution. Let’s understand them in depth to proceed further.

Data Loss Risk: This task can be severe for users as it comprises risks that can result in data loss. Any wrong command can result negatively instead of undo DELETE SQL command.

Reduced Efficiency: This method is not so efficient. Also, we can say that not all users can get the output in proportion to the efforts they put in. In a nutshell, we can say that users might feel the wasting of resources here.

Complex Procedure: Undoubtedly this task is complex for users. So many steps & stages that are too with such sensitive commands. One single mistake & the entire database might get in danger.

Time-Taking Process: This manual procedure is slow & this is why it is directly related to being inefficient. Also, this Undo DELETE command in SQL affects the usual working of the database & reflects a negative impact for sure. 

No Advanced Features: Last but not least, this solution does not comprise any advanced features to make the process automate or at least a little bit a fast. In this era of technology, manual solutions like this are quite irritating.

Undo DELETE SQL with Automated Solution

Now, that we know the manual solution is full of drawbacks, we can also suggest the current trending solution that most DBAs use. SysTools SQL Recovery Tool is what users need as it is totally automated. People all around the world including, experts, MVPs, trusted users, etc trust this utility.

Download Now Purchase Now

Download this advanced utility & then follow the 5 easy steps below to get the deleted back without learning how to undo DELETE in SQL database:

Step-1. Launch Software for SQL undo DELETE task.

Step-2. Click Open to Add Database Files in system.

Step-3. Choose the Quick or Advanced Scan options.

Step-4. Enter the Destination Database Location path.

Step-5. Hit Export to Undo DELETE command in SQL.

Why Automated Solution is Better than Commands?

As we witnessed the automated solution & its quick & easy 5 simple steps, it’s time to have a look at its advanced features & benefits combined. Also, this can help users to get a detailed understanding of why an automated solution is better for undo DELETE SQL queries.

Safe & Secure: The automated solution is quite safe & secure for users. It ensures that the database files are always protected from outside threats.

Capability for Repair: The database does not only recover but also repairs the corrupted data files in the server.

Scanning Modes: There are both Quick & Advance Scan modes available in this tool. To save time & recover files fast, use quick scan & use advance in case of corruption.

Supported Editions: The software supports SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008/ R2, 2005 & 2000 versions. Also, it supports the Windows Operating system.

Selective Recovery: There are plenty of features in this tool that allow users to execute selective recovery. Evidently, One such feature is to recover files with schema or schema with data.

Video Tutorial to Undo DELETE Command in SQL DB

Now, let us go through a video tutorial of this advanced solution for understanding the detailed procedure.

Conclusion

Finally, after going through the detailed article, now users must be aware of the right tool & technique for SQL undo DELETE option. Moreover, we can say that as a whole, the automated solution is the ideal selection for users. The manual solution also works well but its drawbacks are way more than the positive elements.

FAQs

Q-1. Can we rollback DELETE?

Ans. Yes. Delete operations can be rollbacked. However, Drop & Truncate operations can’t. This is because Delete is DML & DROP or TRUNCATE are DDL commands.

Q-2. Is this software free? If not, how can I test it?

Ans. No, this solution is not free but comes with a trial version for testing. Evidently, this is why learning how to undo DELETE in SQL automatically is way better.

Q-3. How to ROLLBACK in SQL Server after DROP?

Ans. As the DROP command is DDL, users can’t undo this. However, they can recover the data using advanced tools like the one mentioned above.

Q-4. Is it safe to use this solution for SQL undo DELETE?

Ans. Yes. Several IT experts, users & MVPs trust this utility because it is completely safe & protects the integrity of the database files.