How to Get Back Deleted Table in SQL

This article discusses the tried and tested solutions on how to get back deleted table in SQL. In this informative post, we will discuss both manual and professional solutions to accomplish the task effectively. So without wasting time, let\'s deep dive into this blog to know more about it.

How to Get Back Deleted Table in SQL Using Manual Solutions

Users can follow the given manual solutions to recover deleted table in SQL Server.

Method 1 Get Back the Deleted Table in the SQL Server Using SSMS

Here are the steps to recover a deleted table in SQL Server versions from 2000 to 2022.

First, open SQL Server Management Studio (SSMS) on your computer.
Now, right-click on the Database Folder and select the Restore Database.
Select the radio button corresponding to your computer and then click the Browse icon (...).
Later, pick the backup media type as File and hit the ADD button.
Choose a Backup file to recover the deleted table from the database and hit OK.
After that, the deleted data from user SQL table has been successfully restored. Users can now view the confirmation message.
Finally, hit the OK button to restore deleted data from the SQL Server successfully.

Method 2 Get Back the Deleted Table in the SQL Server Using the Log Sequence Number (LSN)

#1 Create a new SQL Database

By running the below-mentioned query, the user will create a new SQL database named “RecoverDeletedTables” and a table named Employee with three SQL columns.

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

#2 Now, Insert Data within the SQL Table

Now, the user to insert rows into the SQL table by executing the below query.

USE  RecoverDeletedTables;
GO
INSERT INTO Employee DEFAULT VALUES;
GO 100

#3 Deleted Rows from the SQL Table

We will proceed to remove specific rows from the Employee table by executing the following command.

USE  RecoverDeletedTables
Go
DELETE Employee
WHERE [Sr.No] < 10
GO
Select * from Employee

All the rows having serial number less than 10 will be deleted from the table “Employee”.

#4 Obtain Details about Deleted Rows

Later, the user will get details about the deleted rows by finding the transaction log by running the following query.

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

Once we have the Transaction IDs for the deleted rows, our next step is to determine the timestamp of when these deletions occurred.

#5 Obtain the Log Sequence Number of the LOP_BEGIN_XACT Log Record

To track the exact time when the SQL rows are deleted, we will use the transactions log ID to get the LSN of the LOG_BEGIN_XACT log records of a SQL transaction.

USE  RecoverDeletedTables
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\'

The command above provides details such as the current LSN of the transaction, the timestamp (2021/03/15 19:36:59:337) when the DELETE statement was executed, LSN (00000014:0000001a:0001), and Transaction ID (0000:0000020e).

#6 Get Back Deleted Tables in SQL

To recover the deleted SQL table records, we must convert the LSN values from hexadecimal to decimal format. This conversion requires prepending \'0x\' to the log sequence number, as shown in the code snippet below.

--Restoring Full backup with no recovery.
RESTORE DATABASE RecoverDeletedTables_COPY
    FROM DISK = \'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SysToolsMSSQLBackup RecoverDeletedTables.bak\'
WITH
    MOVE \' RecoverDeletedTables\' TO \'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SysToolsMSSQLBackup RecoverDeletedTables.mdf\',
    MOVE \'RecoverDeletedTables_log\' TO \'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SysToolsMSSQLBackup RecoverDeletedTables.ldf\',
    REPLACE, NO RECOVERY;
    GO
--Restore Log backup with STOPBEFOREMARK option to recover exact LSN.
   RESTORE LOG RecoverDeletedTables_COPY
FROM
    DISK = N\'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SysToolsMSSQLBackup RecoverDeletedTables_tlogbackup.trn\'
WITH
    STOPBEFOREMARK = \'lsn:0x00000014:0000001a:0001\'

Once data is restored, user need to check whether the deleted records have been recovered. The below command will show the first 10 rows of the SQL table that were deleted.

USE RecoverDeletedTables_COPY
GO
SELECT * from Employee

Drawbacks of Manual Methods

The manual method is time-consuming as it involves multiple steps, which makes the whole process difficult.
Loss of data information is very high in the manual method in the process of recovering tables in SQL Server.
Professional technical knowledge of SQL commands is needed to step in the manual method.

How to Get Back Deleted Table in SQL With Ease

If the user does not get any desired result from the above-mentioned solution, then it can use an expert-verified solution i.e. SysTools SQL Database Recovery Tool to recover the deleted table in SQL Server without any difficulty. The tool gets back most of the deleted tables in the SQL Server. It also recovers all the SQL objects such as functions, stored procedures, rules, views, triggers, etc. This software allows the users to retrieve the SQL Server database without any backup. It highlights the recovered deleted tables and records in Red color. The application exports the SQL data into the new and current SQL Server database.

The software supports all MS SQL Server versions such as 2022, 2019, 2017, 2016, 2014, 2012, 2008 R2, 2005, and 2000 versions. It recovers SQL data from malware and SQL injection-affected MDF files. The application offers two different scanning modes, Quick Scan and Advanced Scan, for repairing SQL Server data files. The tool supports all Windows Operating Systems versions (11, 10, 7, 8) with both 64-bit and 32-bit versions.

Follow these steps to recover deleted tables in SQL Server.

Download and run the software application on your local workstation.
Now, press to open and add the MDF file to the application.
Select the Advanced scan mode option to get back deleted attributes.
Later, the user can view the deleted SQL objects in Red color and hit Export.
Next, the user needs to select the export option like CSV, SQL Server Database, and SQL Server Compatible SQL Script.
Lastly, start the export process and save the recovered objects.

Final Words

In this article, we discuss how to get back deleted table in SQL Server using manual and pro solutions, in the manual solution we discuss two methods one is SQL Server Management Studio (SSMS) and the other one is the log sequence number (LSN). In addition, we discuss a smart tool to recover deleted table in SQL Server quickly and efficiently. The user can choose any method, but it is advisable to select a smart solution as it solves other issues as well.

Science / Technology