Recover Deleted Data From SQL Server Table By Transaction Logs - Quickly

Are you looking for a way to recover deleted data from SQL Server table by transaction logs? If yes, then this article will help you to fix it. We covered all possible methods to retrieve deleted records in SQL Server. So without further ado  let's get started 

We all know, SQL Server database is one of the most widely used applications to store, manage, and retrieve huge amount of data effectively. MS SQL is a relational database management system (RDBMS). However, there are times when users face issues while working on the SQL Server. One of the most common scenarios is when a user might accidentally delete records in SQL Server due to various reasons like a mistaken query, an incorrect filter, or simply a human error. Relax! In this article, we’ll discuss all the possible approaches to Recover deleted records in SQL Server. Let's dive into this article to understand the causes behind this issue. 

Why Does Someone Want to Recover Deleted Records in SQL Server?

There are several reasons why users need to roll back deleted records in SQL Server. Here are some common reasons behind this rollback issue: 

1. Accidental Deletion
2. Human Mistake
3. Incomplete or failed backups 
4. Data integrity and compliance 
5. Integration with other system

Approach 1: How to Get Deleted Records From Table in SQL Server? Manually 

LSN stands for Log Sequence Numbers used to identify each record within SQL Server transaction logs. It plays a crucial role in tracking changes made to the SQL Server. With correct knowledge of the deletion time, it's possible to recover deleted data from SQL Server table by transaction logs using LSNs.

Follow the below-mentioned instructions that help you to  recover deleted records in SQL Server versions 2016, 2015, 2014, 2012, 2008, and 2005 versions:

1. First, use the below-mentioned query to determine how many rows are present in the table where the data was erased or deleted.

SELECT * FROM Table_name

2. Next, take the log back with the help of the below-mentioned query:

USE Databasename

GO

BACKUP LOG [Databasename]

TO DISK = N’ D:\Database Name\RDDTrLog.trn’

WITH NOFORMAT, NOINIT, 

NAMEN' Databasename- Transaction Log Backup’, 

SKIP, NOREWIND, DOWNLOAD, STATS = 10

GO

3.  Now, collect details about the deleted entries from the SQL Server database table to recover data.  

USE databasename 

GO

Select [ Current LSN]LSN], [Transaction ID], Operation, Context, AllocUnitName

FROM

fn_dblog(NULL, NULL)

WHERE Operation = ‘LOP_ DELETE_ROWS’ 

Through this query, you will obtain the Transaction ID of deleted records.

4. Run the following query to find the specific time at which the records got deleted using the Transaction ID.

USE Databasename

GO

SELECT 

[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]

FROM

fn_dblog(NULL, NULL)

WHERE

[Transaction ID] = ‘ 000:000001f3’

AND 

[Operation] = ‘LOP_BEGIN_XACT’

with the help of the above-mentioned query, you get the ongoing LSN (Log Sequence Number). 

5. Start the restore process to retrieve deleted data from the SQL Server Table.

Recover Deleted D USE Database name

GO

RESTORE DATABASE Databasename_COPY FROM

DISK = ‘D:\Dtabasename\RDDFull.bak’

WITH

Databasename\RDDFull.bak’

WITH

MOVE ‘Databasename’ TO ‘D:\RecoverDB\Databasename.mdf’, 

MOVE ‘Databasename’ TO ‘D:\RecoverDB\Databsename_Log.ldf’,

REPLACE, NO RECOVERY;

GO

6. Finally, the user can check to see if the SQL Table database has recovered any deleted records.

USE Databasename_Copy GO Select * from Table_name

Approach 2: Recover Deleted Data From SQL Server Table by Transaction Logs Using Professional Solution 

SysTools SQL Database Recovery Tool is the best solution to recover deleted records in SQL Server. The recovery utility allows users to preview and recover deleted database objects like Tables, Triggers, Stored Procedures, Views, Functions, and more. With this, users can easily recover deleted records in just a few clicks. 

Additionally, the tool provides two scan mode options- Quick mode for minor corrupted database files and Advanced mode for hickey corrupted database files. Also, users can recover their data from ransomware-affected MDF files. Moreover, the software application is compatible with all the SQL Server versions including 2022, 2019, 2017, 2016, and the previously released MS SQL versions. 

Step-by-step Procedure to Recover Deleted Record in SQL Server : 

1. Download and run the tool on your Windows System. From the software menu, Click on the “open” button to upload the MDF database file. 
2. Thereafter, choose the scan mode either Quick or Advanced, and select the SQL Server version accordingly. Also, make sure to check the recovered deleted objects dialog box to recover deleted data from SQL Server.
3. The software shows all the recovered deleted data on the software screen, then click on the export option.
4. In the export options window, fill the required details accordingly and choose the export option either “with only schema” or “schema and data both”. 
5. Finally, click on the Export button to restore deleted rows in SQL Server. 

Concluding Words 

In the above blog, we discussed how to recover deleted data from SQL Server tables by transaction logs. We covered two different techniques: manual and professional solutions. However, manual methods are free of cost but have some disadvantages like - requiring good technical knowledge, taking too much time, etc. We recommend you try out the professional solution to recover deleted records in SQL Server without any headache. Plus, the software application provides a user-friendly interface so that even a novice user can also use this tool to solve the issue.