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
GO

SET IDENTITY_INSERT table_1 ON

INSERT INTO table_1 (column_name)
SELECT *
FROM restored_database.table_1

SET IDENTITY_INSERT table_1 OFF

* 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
GO

SELECT *
INTO table_1
FROM restored_database.table_1
GO

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

DBCC CHECKTABLE ("table_1")

Javier Huthon

Javier is Content Specialist and also .NET developer. He writes helpful guides and articles, assist with other marketing and .NET community work