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 or ApexSQL Restore.

Restoring specific table(s) by using ApexSQL Recover

ApexSQL Recover is a SQL Server recovery tool that has a convenient feature to extract data from a database backup that enables quick restoration of only specific tables/rows.

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 specific table(s) by using ApexSQL Restore

ApexSQL Restore is another SQL Server data restore tool that can greatly enhance this process. By virtually restoring a database with ApexSQL Restore, a user will not only save time, but also a large amount of space on the SQL Server.

Instead of restoring a database natively, it is recommended to add a virtual database to the SQL Server that will be fully functional, and can be used to data extraction.

To virtualize a database, following steps need to be executed:

  1. Start ApexSQL Restore and click on the Add virtual database to start the wizard

  2. In the Restore a backup dialog that will appear, following details must be provided:

    1. The name of the SQL Server instance the backup will be attached to and valid credentials
    2. The name of a database in which a backup will be virtually restored to

  3. In the next step of the wizard, specify the backup that will be attached. To do so, click on the Add files button, navigate to the database backup file and all transaction log backups prior to the moment in which unwanted changes have occurred and click Open

  4. If the exact time of unwanted changes is known, change Restore to setting to Point in time, by clicking on the Specific point in time radio button, and specify the time on the timeline

  5. Click Finish to attach the backup

After the backup is virtually restored, it can be used to copy structure and/or data to the damaged table.

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. Both ApexSQL Recover and ApexSQL Restore can greatly enhance the process of extraction and cut the time/space needed for this job significantly.

February 4, 2015