How to Recover Deleted Rows in SQL Server? [Top 2 Methods]

Summary: Suppose you have dedicated hours constructing your database. After that, you discover that a significant group of rows has disappeared without a trace. So, what now? Whether it’s an accidental deletion or a malicious act, it is important to understand the data recovery mechanism. Therefore, today’s article is about how to recover deleted rows in SQL server. We will take you to the right technique and an SQL Database Recovery tool to tackle the situation. Further, let us explore how to get deleted rows in SQL server.

Your database records might get affected, if you’ve accidentally executed a DROP or DELETE command against a table with a wrong WHERE clause. Well, you may lose all or a significant part of the records in your table. In that case, if you do not have any backups or recovery measures, it appears the data is permanently lost. After that, you need to practice every possible way to recover deleted data from table in SQL server. But how?

Solutions – How to Recover Deleted Rows in SQL Server?

Any changes or activities performed on the database are saved in the SQL Server transaction log. This also includes details of each DELETE operation. So, using the built-in function ‘fn_dblog’, you can access all the contents of the current transaction log. This feature of the SQL server helps to examine the transaction log and activities like the update, deletion of tables, and more. Therefore, in the following section, we will explore how to get deleted rows in SQL server.

We have found two prominent methods to carry out this recovery:

1. Recover Deleted Row from Transaction Log using LSN (Log Sequence Number).
2. Using the Professional Recovery Tool

Let us begin with the commands and LSN Recovery Methods. Later, we will discuss the most used and convenient process that is using the professional tool.

#1: Using LSN- Recover Deleted Data from Table in SQL Server

LSN, short for Log Sequence Number, is a binary value. It is a unique identifier to track the location of a particular database transaction within the transaction log. This log is an essential element of a database system recording and storing all activities and changes made to the database. LSNs play a vital role in the recovery process, especially when you have to recover deleted rows from SQL server table. It captures every transaction, including inserting, updating, or deleting data.

Each entry in the log is assigned a unique LSN. Interestingly, the LSN of an entry is higher than any previously executed transaction. Therefore, further, let us examine the steps and figure out how to recover rows which are deleted in SQL server.

Note: If the transaction log backup is damaged or unavailable, the process cannot retrieve deleted records in SQL. You may look for another process.

We will understand how is Log Sequence Number used through an example. The process involves lengthy steps. We will begin with creating a database and a table. Later, insert rows into the table, delete specific rows, and retrieve information regarding the deleted data. Ultimately, we will recover deleted data from table in SQL server. Now, follow the procedure below.

Step 1: Create a SQL Database

For example, we are creating a Database “DeletedRecords” and a table “AprSalary”. Run the following commands to create a Database and the tables:

USE [master]; 

GO  

CREATE DATABASE DeletedRecords;

GO

USE DeletedRecords;  

GO

CREATE TABLE [AprSalary] (

[Sr.No] INT IDENTITY,

[Date] DATETIME DEFAULT GETDATE (),

[City] CHAR (25) DEFAULT ‘City1’);

Step 2: Insert Rows into the Table

Now, run the following query to insert rows and data into the created table:

USE DeletedRecords; GO INSERT INTO AprSalary DEFAULT VALUES; GO 100

Step 3: Delete Some Rows from Table

Next, execute the command below to delete some of the rows you have inserted:

USE DeletedRecords Go DELETE AprSalary WHERE [Sr.No] < 10 GO Select * from AprSalary

So, now all the rows with Serial no. below 10 will be deleted from the table. Further, we will execute steps to restore deleted rows in SQL server.

Step 4: Retrieve Information of the Deleted Rows

In this step to recover deleted rows in SQL server, use the fn_dblog function to query the transaction log. Search through the transaction log to get information about the deleted rows from the table. Run the following commands:

USE DeletedRecords GO SELECT  [Current LSN],    [Transaction ID], Operation, Context, AllocUnitName FROM fn_dblog(NULL, NULL) WHERE  Operation = ‘LOP_DELETE_ROWS’

Once you get the transaction IDs of the deleted rows, filter deleted records according to the time when they were deleted.

Step 5: Get the LSN of the LOP_BEGIN_XACT Log Record

Here, retrieve the Log Sequence Numbers (LSNs) associated with the deletion transactions. Go for the commands given below:

USE DeletedRecords GO SELECT  [Current LSN],    Operation,      [Transaction ID],      [Begin Time],      [Transaction Name],      [Transaction SID] FROM     fn_dblog(NULL, NULL) WHERE     [Transaction ID] = ‘0000:0000020e’ AND     [Operation] = ‘LOP_BEGIN_XACT’

Hence, you got the transaction’s current LSN along with additional information. Finally, you can now perform steps to recover deleted rows in SQL server.

Step 6: How to Get Deleted Rows in SQL Server?

Firstly, you have to convert the LSN values from hexadecimal to decimal format. So, add the prefix ‘0x’ before the log sequence number, as demonstrated in the following code:

Hence, the process to recover deleted rows from table in SQL server is complete.

Verify the Recovered Rows and their Data Integrity

Once the recovery is done, execute the code to check whether all the deleted records have been restored properly:

USE DeletedRecords_COPY GO SELECT * from AprSalary

There is a huge possibility that all data might not be recovered. Well, this is the problem faced when you manually recover deleted data from table in SQL server. Better you switch to a professional SQL Recovery tool for instant and satisfactory results.

Additionally, if you do not come from a tech background, performing SQL codes with ease is a myth. Therefore, to prevent further loss, read the next section.

#2. Best Tool to Recover Deleted Rows in SQL Server- Quick and Easy

Very Often SQL databases get corrupt, resulting in the deletion of tables, records, and other objects, automatically. In that case, download the SysInfo SQL Database Recovery Tool. It will proficiently retrieve deleted rows in SQL without performing lengthy processes. Quick, Secure, and Easy to Execute!

Whether you wish to Restore a Single Table from SQL Database Backup, or multiple deleted records, this tool is the ultimate choice. It is designed to recover and restore SQL database objects like tables, views, triggers, stored procedures, etc. The tool has a dual recovery mode to recover the maximum possible data from the added MDF and NDF files. Furthermore, let us explore the procedure.

Steps to Recover Deleted Rows in SQL Server

Follow the steps below, and recover deleted records from table in SQL server using this tool:

1. Install and run the SQL Database Recovery Tool on your system.
2. Then, on the top-left, click on Open to browse the SQL database files.
3. Once you have added the database file, select the recovery mode.
4. Choose either Show Deleted Records or Advanced Deleted Records.
5. Preview the recovered database tables and select the folders to restore.
6. To save recovered objects, click on Save SQL Data and choose the Saving mode.

You can also run the free trial version of the software to know the working properly.

Conclusion

We hope you are now clear on how to recover deleted rows in SQL server. The manual method looks complicated, but if you are well aware of SQL Database it’s not difficult to perform. However, for new users, the SQL Recovery Tool is the best choice. It is a straightforward method and can be easily operated by all kinds of users. Moreover, it has multiple features that enhance users’ experience.