How to Recover Deleted Table in SQL Server Database?

Rollins Duke   
Published: July 31st, 2023 • 6 Min Read

Overview: In this blog post, we will explain the top 3 ways to recover deleted table in SQL Server database. Hence, keep continue reading this write-up and get the effortless solution to restore deleted SQL table records.

Sometimes Microsoft SQL Server users may execute the Delete / DROP command for associated tables. They accidentally deleted SQL table or transaction records from table. But business users never want to down SQL servers because it impacts their businesses.

Table of Content Hide

Know MS SQL Table and Its Commands

We all know that MS SQL Server uses tables to manage its relational database including all records. A lot of organizations and companies use SQL (Structure Query Language) to fulfill their automation transaction needs.

Businesses and e-commerce websites use SQL Server database tables to store preferred automation records i.e., Employee ID, Employee Name, Address, Phone Numbers, Email Id, etc. SQL table schema may be different accordingly.

SQL Administrators mainly perform the mentioned commands against SQL table to manage them accordingly.

  • DELETE: for deleting a record.
  • DROP: for deleting an entire table including all available records.
  • INSERT: for inserting a record in the table.
  • UPDATE: for modifying SQL table records.
  • CREATE: for creating a new SQL table or object in the SQL database.
  • GRANT: for assigning privilege permissions.
  • REVOKE: to get back assigned privilege permissions.

Recommended: Multiple Ways to Repair Corrupt MDF Files in SQL Server

#1) Recover Deleted Data from Table in SQL Server by SSMS

Just proceed with the below-mentioned steps to recover deleted table in SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 R2, 2005, and 2000.

  1. Start SQL Server Management Studio (SSMS) on your computer.
  2. Thereafter, right-click on the Databases folder and pick the Restore Database
  3. Check the Radio button associated with your computer and click on the Browse icon (…).
  4. After that, choose the backup media type as File and press Add
  5. Select a backup file to restore deleted table from database and hit on Ok.
  6. Thereafter, your deleted SQL table data is restored and now see the message of completion.
  7. Lastly, press the Ok button to successfully restore deleted data from SQL Server table.

#2) Restore Deleted Table in SQL Server Using LSN

Note That: LSN (Log Sequence Number) technique will not allow to recover deleted data from Table in SQL server. If the transaction log backup got damaged and missing. This is mandatory to perform this technique because every SQL log record can be identified by an LSN.

1 Phase: Create SQL Database

Perform the following command for creating SQL database named as “RecoverDeletedTables” and table name as “Employees”.

USE [master];


CREATE DATABASE RecoverDeletedTables;


USE RecoverDeletedTables;


CREATE TABLE [Employees] ()



[City] CHAR (25) DEFAULT ‘City1’);

2 Phase: Insert Data in SQL Table

Using phase 1, we have successfully created SQL database with table including 3 different columns. Now see the insert process.

USE RecoverDeletedTables;



GO 100

3 Phase: Delete Some Rows in SQL Table

USE RecoverDeletedTables


DELETE Employees

WHERE [Sr.No] < 10


Select * from Employee

4 Phase: Know About Deleted Table Rows

Now, this is time to get information about deleted SQL Rows by searching the transaction log data.

USE RecoverDeletedTables



[Current LSN],

[Transaction ID],





fn_dblog(NULL, NULL)


Operation = ‘LOP_DELETE_ROWS’

After using the above-mentioned command, you can see transaction ids of deleted SQL Tables Rows. Here, we need to search the Rows’ deletion time.

5 Phase: Get LSN of LOP_BEGIN_XACT Log Record

Now, this is mandatory to use the transaction id to get extract Rows deletion time.

USE RecoverDeletedTables



[Current LSN],


[Transaction ID],

[Begin Time],

[Transaction Name],

[Transaction SID]


fn_dblog(NULL, NULL)


[Transaction ID] = ‘0000:0000030e’


[Operation] = ‘LOP_BEGIN_XACT’

After that, you can see the information about the Current LSN, Operation, Transaction ID, Transaction Name, etc.

6 Phase: Recover Deleted Tables in SQL Server

Thereafter, you need to change LSN values from hexadecimal to decimal format by adding ‘0x’ before LSN. This is helpful to restore deleted SQL table from database.

–Restoring Full backup with norecovery.


FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\RecoverDeletedTables.bak’


MOVE ‘RecoverDeletedTables’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\RecoverDeletedTables.mdf’,

MOVE ‘RecoverDeletedTables_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\RecoverDeletedTables.ldf’,



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

RESTORE LOG RecoverDeletedTables_COPY


DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\RecoverDeletedTables_tlogbackup.trn’


STOPBEFOREMARK = ‘lsn:0x00000015:0000002a:0001’

Now the process to restore deleted table in SQL server has been completed successfully. If you want to see restored deleted SQL table records, then run the below-mentioned command.

USE RecoverDeletedTables_COPY


SELECT * from Employee

Attention: In the above-mentioned code, replace the location ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\ MSSQL\Backup\RecoverDeletedTables.bak’ with the path where your backup file is saved. Please also replace the path of the MDF and LDF files with the path where you have stored the MS SQL database files.

#3) Recover Deleted Table in SQL Server via Automated Tool

If the above-mentioned methods failed to recover deleted table in SQL Server, then you should use SQL Server Recovery Software. This utility has the capability to repair SQL Server Master Database for restoring deleted SQL tables. The tool supports Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 R2, 2005, and 2000 editions.

Download Now Purchase Now

How to Recover Deleted Table in SQL Server without Backup?

  1. Start the software and press Open to insert SQL database file (MDF).
    upload SQL database
  2. Now choose advance scan mode to recover deleted objects.
    advance scan mode
  3. Thereafter, you can see deleted SQL objects in RED Color and press Export.
    Recover Deleted Table in SQL Server
  4. Afterward, your need to choose the Export option accordingly and click on Save.
    Restore Deleted Table in SQL Server
Benefits of Software
  • Restore deleted table in SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 R2, 2005, and 2000.
  • The tool offers multiple export choices i.e., SQL Server Database, SQL Server Compatible Scripts, and Standard CSV File Format.
  • Restore dropped table in SQL server corrupted and damaged database without using any command.
  • The software is authorized to repair SQL Server database for restoring all deleted tables and other objects.
Final Thoughts

However, we have discussed most used 3 best ways to recover deleted table in SQL Server. But this is advisable to go with manual steps to restore deleted table in SQL server, if you have SQL Server administrator-level knowledge. If in case, you are a non-technical user then must use the recommended toolkit to resolve your query instantly.

Read Also: Know How to Fix SQL Server Error 18456