How to Recover Deleted Tables in SQL Database [Guest Post]

This post is written by: Priyanka Chouhan

Priyanka Chouhan is a technical writer in "Stellar Data Recovery" with 5 years of experience and has written several articles on SQL. She has the fine knowledge of SharePoint and SQL Server. In the spear time she loves reading and gardening.

The SQL Server deleted table can be recovered using transaction log and Log Sequence Number (LSN). Transaction logs are responsible for logging the database activities. Using the undocumented function “fn_dblog” you can find out the unauthorized deletion of data and can recover back the deleted data contents. 

If the estimated time and record of data is known, it is not a difficult task to recover your data. In case, if you are not aware of the date and time of its deletion, then it requires detecting the real cause of deletion. The initial task is to find out the accurate log sequence numbers (LSN) under which the DELETE statement is executed, and then the data will be recovered until the exact LSN is reached.

What is an LSN?

Each record in SQL Server database transaction log is identified by a unique LSN. It is associated with every record at which the substantial event occurred. This may be helpful for building accurate restore sequence. LSNs are used internally for tracking the point in time at which data is restored during the RESTORE sequence. While restoring a backup, the data is restored to LSN, which is associated with that point in time during which the data was recorded. 

Restore Deleted SQL Database Tables with Log Sequence Number

SQL Server deleted data can be restored easily by overwriting the original database using backup files. If the DROP TABLE statement is executed accidently, and the full database backup is available, it can be considered that no modifications were made after the table was dropped.

If this is not the case, deleted data can still be recovered even if:

• the database objects has been modified after executing the DROP TABLE statement,
  or
• valid database backup is not available

Every DROP operation is logged to the transaction log in SQL Server. This implies that the transaction log saves information required to roll back transactions, considering that the SQL Database is in full recovery mode and supports point in time recovery from the respective DROP TABLE statement.

If the DROP SQL database operation is logged to the SQL Server transaction log, it is possible to roll back the operation even if it is compressed within an active transaction.

DROP TABLE Customer1
SELECT * FROM Customer1

Since the table ‘Customer1’ has been dropped, the above SQL Query will result in “Msg 208, Level 16, State 1” error message.

Since the DROP operation is rolled back, the following SQL Query will return entire rows existing in the Customer1 table.

BEGIN TRAN 
DROP TABLE Customer1
ROLLBACK 
SELECT * FROM Customer1

Can Dropped Tables be restored? 

In case, the transaction with DROP operation is no longer active, the table Customer1 cannot roll back since it has been committed. Therefore, the data it stores will be deleted. The question that now arises is whether the data being deleted due to DROP operation can be recovered even if the full backup is not available. The answer is yes. SQL tables can be recovered using third party software even in cases where the backup is not available. 

Follow the steps below to recover SQL Server deleted table data and gain access to integrated attributes with the help of Stellar Phoenix SQL Database Repair:

1. Download, install and launch the software.
2. The home screen will appear with a message box stating that the SQL Server must be stopped before initializing the recovery process.
3. In the next window, select the database (MDF file) in which table has to be restored.
4. Under Find Database column, provide the location of the folder to search the database.
5. Alternatively, click the Find button if the database location is unknown.
6. You can select the option to recover deleted records as well by checking the box which reads “Include Deleted Records”.
7. Select the Repair button to initiate the procedure to recover the SQL Server deleted tables.
8. The tool will display all the items of the database including tables, indexes, Views, Triggers, Rules, Functions, etc. on the left panel.
9. You can check the boxes available against each item to recover the specific item. In this case, you need to select Tables.
10. Next, preview all the details incorporated in the selected table including deleted records will be displayed on the right panel of the tool.
11. The Electronic Registration wizard will open. Enter the Serial Number delivered to your Email ID.
12. The tool will provide the option to save the recovered database into four file formats, namely, MSSQL, CSV, HTML, and XLS.
13. Next, click on the Browse button to provide the destination path for storing the recovered MDF file.
14. Next, click on OK to proceed further with the process to restore the deleted SQL Server table.
15. You can optionally select to open the recovered database by checking the box “Automatically open the destination folder after saving”.
16. Then, click on OK.
17. Select the Saving option to restore the recovered tables from the MDF file either from: 
1. New Database: Selecting this option restores recovered data to new database, or
2. Live Database: Selecting this option restores recovered data to existing database
   
   Note: If you select to store recovered data to the existing database, you need to provide the SQL Server Database name and other credentials.
18. Next, the message box will appear stating that the recovered data is restored to the selected format

You can now view the newly stored database or MDF file to access the recovered tables.