How to Recover Deleted Table From SQL Server

Nilesh Kumar | Modified: July 31, 2023 | SQL Server | 8 Minutes Reading

Summary: In this write-up, we will discuss some simple and effective methods that you can use to recover deleted table from SQL Server. So, keep reading this post because you are very close to know the solution.

MS SQL Server is an extremely admired relational database management system among business & corporate sectors. It stores all its data in MDF, NDF, and LDF database files. Sometimes, SQL database tables are deleted by users during the management and maintenance of the databases, due to which administrators face many serious issues. Hence, it is important to recover deleted data from SQL server to get back to the normal workflow.

So if you are also such a user who needs quick and reliable solution for how to recover deleted table from SQL Server then this platform is the right place to overcome this circumstance. Here, you will get the manual as well as an automated solution for retrieving deleted table from the SQL Server database. Before jumping onto the solution part it is necessary to know the reasons for data loss in SQL Server. This way users can execute SQL server recover dropped table in a better way.

Possible Reasons Behind SQL Server Data Loss

Damaged database files (.mdf/.ndf)
Corrupted file system
Virus attack
Human error
Problem with the hard disk
Sudden power failure

MS SQL Server Table Restore – Common Commands to Know

It is a well-known fact that the SQL Server database stores data files in the form of tables. This even includes records & all other data as well. This system is so robust that it’s still in high demand almost after 49 years of introduction. Mainly to satisfy the data management needs.

For example, businesses use this SQL database for storing the details of their customers, staff, inventory, etc. An employee database includes fields like Employee ID, Employee Name, Address, Age & Sex, Phone Numbers, Email Id, etc.

Well, there are plenty of commands for various functions. Also, the commands for learning how to recover deleted table in SQL Server are quite complex. However, the most common ones for a table are:

CREATE: Creates a new SQL table or object in the database.
INSERT: Insert records in table for data storing & management.
UPDATE: It modifies or updates records in SQL table if needed.
DELETE: It deletes a single or selective record based on needs.
DROP: Delete an entire table including all of the available records.
GRANT: Assigns the privileges & permissions to users working on it.
REVOKE: This is used to get back the assigned privileges & permissions.

Restore Deleted Data from Backup Using SSMS

Follow the below-mentioned instructions to recover deleted table from SQL Server using the backup file:

Step-1. Launch SQL Server Management Studio (SSMS) on your machine. Right-click on the databases folder and select the Restore Database option.
Step-2. Afterward, check the radio button corresponding to From Device and then hit the browse button (…).
Step-3. Here, select backup media type as File and click on the Add button.
Select the backup file to restore the database and press the OK button.
Step-4. Now, your database is restored successfully, a confirmation message will pop up simply hit the OK button.

How to Retrieve Deleted Table in SQL Using LSN Method

Now, as we know that the Log Sequence Number or LSN method can’t help in restoring the deleted data. It only restores the damaged & missing transaction log data. In such cases, this technique is quite useful.

There are a total of six steps in this technique, so let’s explore them one after another with their respective commands.

Step-1. Create A Database First

First of all, all that users need to do is just create a database. For example, here we are creating a database named TestCompany & tabel named “Employees” to proceed.

USE [master];
GO
CREATE DATABASE TestCompany;
GO
USE TestCompany;
GO
CREATE TABLE [Employees] ()
[Sr.No] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT ‘City1’);

Step-2. Insert Data in Newly Created SQL Table

As we created the table in SQL database in the above step, it’s time to add data in the columns we created. Follow the below command for that:

USE TestCompany;
GO
INSERT INTO Employees DEFAULT VALUES;
GO 100

Step-3. Delete Rows in Your SQL Table

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

Step-4. Check Deleted SQL Table Info

Now, users need to search for the log data files for acquiring information of the deleted table. The command for the same is:

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

Now, we have got the transaction IDs of the rows deleted in SQL Server table. It’s time for users to find out the time when the rows were deleted.

Step-5. Know LSN of LOP_BEGIN_XACT Log Record

The transaction ID is mandatory here for users to proceed & find the deletion time. Follow this command to get the desired results:

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

It shows the Current LSN, Operation, Transaction ID, Transaction Name, etc details to users.

Step-6. How to Recover Deleted Table in SQL Server

Afterward, users need to change the LSN values from hexadecimal to decimal format. This should be done by prefixing each LSN with ‘0x.’ This is the most crucial part for users to recover deleted table in SQL Server safely.

–Restoring Full backup with norecovery.

RESTORE DATABASE TestCompany_COPY
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\TestCompany.bak’
WITH
MOVE ‘TestCompany’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\TestCompany.mdf’,
MOVE ‘TestCompany_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\TestCompany.ldf’,
REPLACE, NORECOVERY;
GO

–Restore Log backup with STOPBEFOREMARK option to recover exact LSN.

RESTORE LOG TestCompany_COPY
FROM
DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\TestCompany_tlogbackup.trn’
ITH
STOPBEFOREMARK = ‘lsn:0x00000015:0000002a:0001’

To view the restored database records, users must go through this command:

USE TestCompany_COPY
GO
SELECT * from Employee

Professional Way to Recover Deleted Table From SQL Server

The above-mentioned manual method does not work if you don’t have a healthy backup of your database. In such a situation, you can take the help of an Enterprise-Grade Level professional SQL Database Recovery tool. This software has the potential to recover deleted data from SQL Server without losing a single bit of data. By using this software, the user can also recover the permanently deleted data including views, triggers, functions, rules, etc. in just a few simple clicks and restore SQL database with different name. This tool shows the preview of the deleted database items in red color so that it can be easily recognizable by the user.

Also, by using this utility users can easily recover the database items from damaged or corrupt SQL database files and restore MDF file in SQL Server. The graphical user interface of this tool is simple and easy to use so that a technical and non-technical user can easily use it. In a nutshell we can say that, for SQL server recover dropped table operation, it’s an ideal choice.

Steps to Recover Deleted Table From SQL Server

Step-1. Download the Software and install it in your Windows OS system.

Download Now Purchase Now

Step-2. click on the Open button to load the SQL database file (.mdf file).

Add MDF file

Step-3. Select a scan mode and then choose the Server version of MDF file either manually or automatically. Also check the box corresponding to the Recover Deleted Objects option to recover deleted database objects from SQL Server and click the OK button.

Choose scan option
Step-4. Once the scanning process gets completed, preview deleted database items and press the Export button to export the recovered database.

Preview Database
Step-5. Afterward, select an export option (SQL Server Database or SQL Server Compatible Scripts or CSL File Format) and fill all the export details accordingly. Finally, hit click the Export button for SQL server recover dropped table.

Chose database objects

Also Read: How to Recover Deleted Objects in MS SQL Server Without Hassles

How to Retrieve Deleted Table in SQL & What Features Do We Have?

Ability to restore deleted tables in SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 / R2, 2005, and 2000.
There are several export options present for users i.e., SQL Server Live Database, SQL Server Compatible Transcript Files, and Standard CSV File Format.
Easily recover the dropped table in SQL server having corrupted table data without using any complex commands.
This software is enabled with advanced algorithms that can help users counter all kinds of errors.
Also, this advanced solution is even capable of recovering ransomware-affected database files in a safe & easy manner.

Final Words

Well, there are times when SQL Server users face disastrous situations in their databases. The scenarios could take place when the user accidentally deleted tables from the SQL Server database. Therefore, users must be aware of the methods to recover deleted table from SQL Server in case of any misadventure.

In this post we have mentioned both the manual as well as an automated solution that you can use to recover deleted data from SQL Server. Just go through the blog and choose whatever option works best for you.