How to Restore Deleted Transactions...

This guide will discuss how to restore deleted transactions in SQL Server in a compressive way using both manual and automated methods. Users can easily understand the whole concept.

It mentions the stepwise process to recover deleted records from the SQL table using the Log Sequence Number (LSN). It also mentions SQL database recovery software that can quickly and easily recover deleted records from the database file.

How to Restore Deleted Transactions in SQL Server Manually

You can follow the methods to restore the deleted transaction in MS SQL Server.

Method 1 Use Log Sequence Number (LSN)

If users know when the SQL record is deleted, you can use the Log Sequence Number (LSN) to restore the deleted transactions in the SQL Server and their particular records from the database. A Log Sequence Number is a special identifier given to each record or table in an SQL transaction log.

Step 1 Create a SQL Database

By running the following command, we will create a database named DB_database and an SQL table named Emp_db with three columns.

USE [master];  
GO   
CREATE DATABASE DB_database;
GO 
USE  DB_database;
GO
CREATE TABLE [Emp_db] (
[Sr.No] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT 'City1');

Step 2 Insert Data into the table

Now, start inserting rows into the SQL table by executing the following command

USE DB_database;
GO
INSERT INTO Emp_db DEFAULT VALUES;
GO

Step 3 Delete the Row from the table

Now, we will start deleting some SQL rows from the table

USE DB_database;
Go
DELETE Emp_db
WHERE [Sr.No] < 10
GO
Select * from Emp_db

Step 4 Get Information about Deleted Rows

We will get detailed information about deleting rows by searching the SQL transaction log by running the command

USE DB_database;
GO
SELECT
[Current LSN],   
[Transaction ID],
Operation,
Context,
AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE
Operation = 'LOP_DELETE_ROWS'

Step 5 Get the Log SQL Number (LSN) of the LOP_BEGIN_XACT Log Record

To locate the exact time when the SQL rows were deleted, we will use the transaction ID to get the LSN of the LOP_BEGIN_XACT log record of a SQL transaction.

USE  DB_database;
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'

Step 6 Restored Deleted SQL Records

To recover the deleted SQL database records, we must convert the LSN values from hexadecimal to decimal format. To accomplish so, we must append '0x' before the log sequence number (see the code below).

--Restoring Full backup with no recovery.
RESTORE DATABASE DB_database_COPY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MS SQL\Backup\DB_database.bak'
WITH
    MOVE 'Database_db' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MS SQL\Backup\Database_db.mdf',
    MOVE 'RecoverDeletedRecords_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SysTools\MS SQL\Backup\Database_db.ldf',
    REPLACE, NO RECOVERY;
    GO
--Restore Log backup with STOPBEFOREMARK option to recover exact LSN.
   RESTORE LOG Database_db_COPY
FROM
    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SysTools\MS SQL\Backup\Database_db_tlogbackup.trn'
WITH
    STOPBEFOREMARK = 'lsn:0x00000014:0000001a:0001'

Cons of Manual Method

The manual method is much longer and time-consuming.
To complete the process, one needs to know SQL commands.
This method is quite complex and difficult to understand.
Loss of information is very high in the manual methods for SQL.

Automated Tool How to Restore Deleted Transactions in SQL Server

Want to restore deleted transactions in SQL Server quickly and reliably, then use the SysTools SQL Log Analyzer Tool. The tool reads and analyzes all SQL Server transaction log (LDF) files. Users can preview all the records, such as Insert, Delete, and Update, using the software. Users utilize, the SysTools SQL Log Analyzer Tool for forensic analysis of the changes in the MS SQL table in a detailed manner. It opens, analyzes, and reads all MS SQL Server transactions. Analyze all the SQL transaction log files to discover all the vital changes in the SQL records.

Procedure to learn how to restore deleted transactions in SQL Server using automated

Download and run the software application on your local computer.
Open and add the LDF/MDF file to the software.
Now, the user has two options to add the file, i.e. Online DB Option and Offline DB Option.
Users can preview all the SQL database details like the Number of records, Number of updates, Number of inserts, and Number of deletes.
Select the Transaction option to sort the details of SQL transactions. In descending and ascending order.
Choose the transaction record type like Delete, Insert, and Update.
Select the export option to save SQL records such as CSV files, SQL Server Compatible Script, and SQL Server Database.
Now, click on the export button and save the file.

Salient Features of Recommended Software for Restoring Deleted transaction in SQL Server

The tool opens, reads, and analyzes all the MS SQL Server transaction log files such as Insert, Delete, and Update.
It allows SQL users to preview all the MS SQL transactions, for example, Insert, Delete, and Update on the preview panel of the tool.
The software application supports all MS SQL versions like 2022, 2019, 2017, 2016, 2014, 2012, 2008 R2, 2005.\ and 2000 version LDF files.
The application enables users to perform forensic analysis on SQL log files to examine SQL Server activity.
Supports customized filters to export only particular SQL transaction records to any different SQL Server database.
With this utility, you can learn how to restore deleted transactions in SQL Server without any trouble.
The application provides different export options to the user, such as CSV file format, SQL Server Compatible Script, and SQL Server Database.
The software supports the advanced versions of SQL data types like geography, SQL_variant, Datetime2, geometry, hierarchy ID, and datetimeoffset.
Users can use the tool in both online and offline versions according to their requirements.
The software runs all versions of the Windows operating systems, such as Windows (11, 10, 8, 7, and XP) for both 64-bit and 32-bit versions.
The tool is used to fetch and view SQL records from a live MS SQL Server database.

Conclusion

Using this guide, users can easily learn how to restore deleted transactions in SQL Server. We covered two approaches - manual as well as automated solutions. However, in case manual methods fail to resolve the problem, you can use the above-mentioned, automated tool. It is an efficient and effective way to recover deleted data from the SQL Server.

Varun Pal

Blog

How to Restore Deleted Transactions in SQL Server: A Step-By-Step Guide