Summary: In this article, we will discuss the top solution for how to get back deleted table in SQL Server database. Whether it was accidentally deleted table by the user, then this article is best for you to recover the table in a hassle-free way. It mentions the step-by-step process using manual (LSN & SSMS) and automated solutions. Follow this article to gain better insights into the recovery process of deleted tables in SQL Server databases.
User Problem
Currently, I have deleted one table from the database. But now I want some of the information from that table.
Please suggest how to get back deleted table in SQL.
I am utilizing SQL Server 2019.
How to Get Back Deleted Table in SQL Server? Manually
To recover deleted tables in SQL Server, users can follow the below-mentioned solutions to easily get back data in the SQL database. Here we will discuss two solutions: SSMS and LSN to restore the deleted table.
Method 1: Recover the Deleted Table in SQL Server Using SSMS
The user can easily get the deleted table back in SQL Server using SQL Server Management Studio. Follow the steps mentioned below.
- First, open your computer's SQL Server Management Studio (SSMS) to start the recovery process.
- Later, right-click on the Database folder and select the Restore Database option.
- Choose the Radio button tab related to your computer. Now, pick the Browse icon.
- After that, select the backup media type as the file option and hit the Add tab.
- Now, choose the backup file to get back the deleted table in the database and press the OK tab.
- After that, the user-delete table is restored, and the process message will be seen.
- Finally, hit the OK tab to terminate the process and get back the deleted table in SQL successfully.
Method 2: How to Get Back Deleted Table in SQL Using Log Sequence Number (LSN)
What is LSN in SQL Database?
In the SQL Server database, LSN stands for Log Sequence Number. LSN is a unique identifier assigned to every logical modification and transaction log record in the SQL database. This comprises SQL commands such as insert, update, and delete. It is a timestamp for changes in the SQL Server transactional log.
If the user has the exact timestamp when the table record is deleted, then the user can easily use the Log Sequence Number (LSN) to get the deleted record back in the SQL Server.
Note: To perform this solution, the user must make sure that their transaction log backup is in healthy form.
Step 1: Create a SQL Database.
The user needs to execute the below-mentioned command to create the SQL database named "GetbackDeleteTable” and a table named "Employee”.
USE [master];
GO
CREATE DATABASE GetbackDeletedTable;
GO
USE GetbackDeletedTable;
GO
CREATE TABLE [Employee] (
[Sr.No] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT 'City1');
Step 2: Insert Data into the SQL Database Table.
In step 1, we create a new SQL database with a table including three distinct columns. Now, we’ll insert rows into a table by executing the below command.
USE GetbackDeletedTable;
GO
INSERT INTO Employee DEFAULT VALUES;
GO 100
Step 3: Delete the Row in the SQL Table.
Now, after inserting some rows, the user will delete some rows in the SQL table.
USE GetbackDeletedTable
Go
DELETE Employee
WHERE [Sr.No] < 15
GO
Select * from Employee
After running the above-mentioned command, rows with serial number 15 will be deleted from the table "Employee.".
Step 4: Know Details About Deleted Table Rows
Now, in this section, we will get detailed information about deleted table rows by searching the SQL transactional log data. For this run, use the following command.
USE GetbackDeletedTable
GO
SELECT
[Current LSN],
[Transaction ID],
Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'
After the user knows the detailed Transaction ID of the deleted table, they will easily find the time when the rows were deleted.
Step 5: Get the Log Sequence Number (LSN) of the LOP_BEGIN_XACT Log Record.
Knowing the exact time when the rows were deleted is necessary to use the transaction ID to get the exact row deletion time using the LOP_BEGIN_XACT log table records of the SQL transactions.
USE GetbackDeletedTable
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 above-mentioned command will display the current LSN (00000014:0000001a:0001) of the SQL transaction, operations, transaction ID (0000:0000020e), transaction name, and the time (2021/03/15 19:36:59:337) when the DELETE statement was executed. Step 6: Recover the Deleted Table Records in the SQL database.
Step 6: Get Back the Deleted Table in SQL
Afterward, to restore the deleted table records in the database, the user needs to convert the LSN values from hexadecimal to decimal format. To do this, users need to add “0x” before the log sequence number (LSN).
--Restoring a full backup with no recovery.
RESTORE DATABASE GetbackDeletedtable_COPY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SysTools\MSSQL\Backup\GetbackDeletedtable.bak'
WITH MOVE 'GetbackDeletedtable' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SysTools\MSSQL\Backup\GetbackDeletedtable.mdf',
Move 'GetbackDeletedtable_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SysTools\MSSQL\Backup\GetbackDeletedtables.ldf',
REPLACE, NORECOVERY;
GO
--Restore Log backups with STOPBEFOREMARK option to recover exact LSN.
RESTORE LOG GetbackDeletedTables_COPY
FROM
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SysToosl\MSSQL\Backup\GetbackDeletedtables_tlogbackup.trn'
WITH STOPBEFOREMARK = 'lsn:0x00000014:0000001a:0001'
Now, the procedure to get back the deleted table records on the SQL Server is complete. Execute the below-mentioned command to restore the deleted table records in SQL Server.
USE Getback DeletedTable_COPY
GO
SELECT * from Employee
How to Get Back Deleted Table in SQL Server Using an Automated Tool
If the above-mentioned solutions fail to get the deleted tables back in the SQL Server, the user should use an expert-recommended software called SysTools SQL Recovery Tool. This tool repairs and recovers all the SQL database files with ease and regains all its main functions quickly. The main feature of this tool is that it recovers deleted tables and records in SQL Server easily. It allows the previewing and recovery of SQL Server main objects such as triggers, functions, rules, views, stored procedures, tables, etc. Export SQL data easily into the current and new SQL Server databases. Display recovered deleted records and objects in Red color for better understanding.
This software supports all current SQL Server versions, such as 2022, 2019, 2017, 2016, 2014, and up to 2000 versions. It offers two types of scanning options to the user. One is a Quick scan for a normal level of corruption in the database files, and the other is an Advanced scan for high-corruption database files and deleted records and objects. Recover SQL data from SQL injection and malware-affected primary (MDF) files. This software offers multiple export options to users, such as CSV File Format, SQL Server Database, and SQL Server Compatible SQL Script. It recovers all SQL keys, like the Primary key, Foreign Key, etc. This tool comes with a free demo version to test all the functions for a better understanding.
Follow these simple steps for how to get back deleted table in SQL Server.
- First, download and run the software.exe file on your computer.
- Now, click on the MDF file to open it and add the file to it.
- Select the scan mode. Quick Scan and Advanced Scan.
- Choose Advanced Scan Mode to recover deleted tables and objects.
- Now, the user will see the deleted table objects in red color.
- Hit the export button to save the deleted tables on your computer.
Conclusion
In this article, we discuss the top solution for how to get back deleted table in SQL Server. If the user uses manual solutions, like SQL Server Management Studio (SSMS) and Log Sequence Number (LSN), then in-depth knowledge is required for this. However, to tackle this problem, we recommended an automated solution to recover deleted tables in SQL Server quickly and efficiently without any problems.