Did you drop a table using the DROP statement and realized it has some data you still need? What if you need to recover the data but don’t have a database backup to restore the dropped table data. There’s still a way to recover the dropped table data without backup, providing your database uses a FULL recovery model; also, the transaction to execute DROP TABLE statement must not be committed after the table was dropped.
This article will explain the methods to recover SQL data from a dropped table without backups.
Methods to Recover SQL Data from a Dropped Table Without Backups
There are two methods you can use to recover SQL data from a dropped table with no backups:
Method 1 – Rollback the Dropped Table
Each DROP operation you perform is logged in a database transaction log. So, if the DROP TABLE command executed within the transaction is uncommitted, it can be rolled back.
Let’s demonstrate an example, where we will DROP a table, say ‘EMP’ from a SQL database ‘Test_Database.’ Next, we will roll back the dropped table. The steps are as follows:
Step 1: Execute the following DROP statement to drop the ‘Employee’ table:
| DROP TABLE EMP |
The table ‘Employee’ will get dropped.
Step 2: Run the SELECT query as follows:
| SELECT * FROM EMP |
This query will result in the below error message:
Msg 208, Level 14, State 1, Line 1
Invalid object name ‘EMP’
Step 3: Now execute the below query to rollback the dropped table:
| BEGIN TRAN DROP TABLE EMP ROLLBACK SELECT * FROM EMP |
After executing the query, you can view all the rows in the EMP table after the DROP operation is rolled back.
Note: If the transaction that has the DROP operation is committed, the dropped table won’t roll back; this may also cause loss of stored data.
Method 2 – Use Database Snapshot
Note: This method applies to users who have dropped a table from the database and created a snapshot of that database.
Check if you have created a database snapshot of the database containing the dropped table. A database snapshot is a static, read-only view of the database in SQL Server. Before a database page is modified, the original page is copied from the database to the snapshot. Thus, a database snapshot preserves the data when the snapshot was created. So, you might be able to revert the database to the state before you dropped a table.
Note: You cannot revert the database from a snapshot is the database is corrupted.
Here’s the syntax to restore a database using the snapshot:
| RESTORE DATABASE db_name FROM DATABASE_SNAPSHOT =db_snapshot_name |
Here, replace ‘db_name’ with the name of your database and ‘db_snapshot_name’ with the name of the database snapshot.
Final Words
You may have intermittently or accidentally dropped a table in SQL Server. If you don’t have backups to restore the database with all the data, including the dropped table, you can recover the table data by the methods discussed in this post.
If your database with the dropped table has a FULL recovery model, and the transaction containing that table is active, try to roll back the dropped table. Another method you can use to retrieve the dropped table data is to revert the database to the snapshot created before the table was dropped.
But, keep in mind, what happened once can happen again. So, ensure to take frequent backups of your database that you can restore to regain access to the data. Also, creating a database snapshot can help revert the db to the state before you dropped the table. However, you cannot take a backup or create a snapshot of a corrupted SQL database. If your database is corrupted, using a SQL Repair tool like Stellar Repair for MS SQL may come in handy. The tool can also help recover deleted records, stored procedures, triggers, and other SQL database objects.
