How to restore specific table(s) from a SQL Server database backup

If restoration of an original database to a previous state is not an option, in the case of accidentally updated, dropped, deleted, damaged data, it is still possible to restore these tables if a database backup was created prior to the accident.

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:
   1. 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
   

   2. 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")

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.

There is an alternative to full backup restoration by utilizing ApexSQL Recover, a SQL Server recovery tool which uses information inside MDF and LDF files (and transaction log backups) to recover data lost due to delete, truncate or drop table operations as well as allows extraction of table structure and data directly from database backups without having to perform backup restore jobs.

To restore only specific tables/rows, following steps need to be executed:

1. Start ApexSQL Recover and select the option to extract From database backup



2. Click on the Add button and select a database backup and all transaction log backups prior to the moment in which unwanted changes have occurred



3. In the next step, check only tables that need to be recovered and uncheck the others



4. In the recovery action step of the wizard, choose between the Save recovery script to file and Recover to new database. The first option will create a script on the provided file location that contains the data which needs to be manually executed on a SQL Server database, and the former choice will directly create a database with the appropriate data. It is up to a user to decide which choice suits them better.



5. Regardless of the choice in the previous step, one last step remains, and it is the same for both choices – selecting the extraction option. Depending on the type of restoration, choose between extracting table structures, extracting data, or both and click on the Extract button to complete the process

After the process is complete, the extracted data/structure can then be copied to the original database, as described in the first part of the article.

Restoring a backup in order to extract only single or couple of tables can be time and space consuming process which may interfere with process of getting the database back to the healthy state. ApexSQL Recover can greatly enhance the process of extraction and cut the time/space needed for this job significantly.