How to Restore Specific Table(s) from SQL Server Database

In previous article, we have written tutorial about how to fix Primary Filegroup is Full in SQL server. In this article, we will continue new tutorial about how to restore specific table(s) from SQL server database.

Sometimes, you might accidentally drop or delete your tables, so you want to recover this table. But, make sure that you also have your database backup. So, how can we achieve this?

Steps to Recover Your Tables

This can be achieved by restoring the latest backup prior to the accident into a different database, and then copying tables back to the original database. Since the retrieved data is not consistent with the current state of the database, referential integrity might be broken. Also, constraint and key errors may occur when the data is copied back in to the original database.

To forestall this, appropriate steps to address any referential integrity issues must be taken and all indexes, full-text indexes, triggers, and constraints must be recreated if the original table was lost.

To achieve this, the following steps need to be executed:

1. Natively restore the latest SQL backup prior to accident along with all log backup files to a new database on the same SQL Server.

2. Copy the data from the new to the original database. Depending on the accident nature (table was dropped, or only rows have been deleted) this can be achieved in the following ways:

* Rows were deleted and the table still exists. Use the following code to insert only the missing rows to the table in the original database

USE original_database


INSERT INTO table_1 (column_name)
FROM restored_database.table_1


* Table was dropped and needs to be fully recreated. The SELECT INTO statement will recreate (copy) both table structure and data back to the original database:

USE original_database

INTO table_1
FROM restored_database.table_1

3. If indexes, full-text indexes, triggers or constrains existed on the original table, they need to be recreated and any referential integrity issues need to be manually resolved

4. To verify the data integrity, execute the following statement


The main problem with this solution is the fact that database backup has to be fully restored in order to extract only specific table(s). This process can take a lot of time or demand substantial amount of free space on the SQL Server, which are not always available.


We hope above tutorial help you to recover your missing table. Happy Coding!

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *