How to recover a single table from a SQL Server database backup

When you ask a DBA what feature they would most like to see added to the SQL Server recovery functionality, they will undoubtedly say: “The ability to recover a single table from a database backup”.

Of course, it is possible to retrieve the data of a specific table from a database backup, but you cannot do it without restoring the entire backup.

Why do DBA’s want this? Well, the reasons are simple:

1. More often than not, the rest of the database is fine, except that someone accidentally damaged or dropped a specific table
2. Restoring an entire backup can take a significant amount of time, and generally, when a table has been damaged to such an extent that a restore is required, the DBA will probably be experiencing pressure to get it done fast

If I would have to guess at why Microsoft has not provided this functionality, I would have to say that it probably relates to integrity concerns. If you extract only one table from a backup, which is not at the same point of time as your current database, it might result in referential integrity being compromised. Of course, this is also true if you restore the entire database backup and then just copy out the specified table, so either way, this is something which will always have to be taken into account when performing a single table recovery.

Here is a diagram of the steps which will need to be performed to recover a single table from a backup:

1. Restore the latest SQL database backup and all log backup files up to the point in time where the data was last known to be correct, as a database with a different name on the same server

   This is the part of the process which is likely to take longest to complete, depending on the size of your database

   If you only need to extract the data for a couple of tables, it may be faster to use ApexSQL Recover to extract the table data into a new database, instead of having to restore the entire backup. I will show you how this can be done a little later in this article

2. Copy the data out of the backup into the target database

   Depending on the scenario in question, you may use one of the following techniques:

   1. If the table still exists, but only some rows were deleted

      Use and INSERT statement combined with a SELECT statement to insert only the missing rows back into the table

      USE AdventureWorks2012
      GO
      SET IDENTITY_INSERT Production.Illustration ON
      INSERT INTO Production.Illustration 
      (IllustrationID,Diagram,ModifiedDate)
      SELECT * FROM AdventureWorks2012_Restored.Production.Illustration
      SET IDENTITY_INSERT Production.Illustration OFF
      

   2. If the table was completely dropped

      Use the SELECT INTO statement to copy the rows and the table structure back into the database

      USE AdventureWorks2012
      GO
      SELECT * INTO Production.Illustration 
      FROM AdventureWorks2012_Restored.Production.Illustration

   3. If some rows were damaged perhaps through a malicious update or some other unwanted event

      The MERGE statement may be used to update damaged or insert data which is missing, or even delete rows which should not be there

      USE AdventureWorks2012
      GO
      SET IDENTITY_INSERT Production.Illustration ON
      MERGE Production.Illustration dest
      USING (SELECT * FROM AdventureWorks2012_Restored.Production.Illustration src) AS src
                    ON dest.IllustrationID = src.IllustrationID
      WHEN MATCHED THEN UPDATE 
      SET dest.Diagram = src.Diagram, dest.ModifiedDate = src.ModifiedDate
      WHEN NOT MATCHED THEN INSERT
      (IllustrationID,Diagram,ModifiedDate) VALUES 
      (src.IllustrationID,src.Diagram,src.ModifiedDate);
      SET IDENTITY_INSERT Production.Illustration OFF

3. Recreate any indexes, triggers, or constraints if this is required

4. Resolve any referential integrity issues

   This is a manual process and unfortunately there is no way around it. It simply has to be done

5. Run the DBCC CHECKTABLE command on the table to verify the data integrity

   DBCC CHECKTABLE ("Production.Illustration")

As promised earlier, I will now illustrate how you can use ApexSQL Recover to extract that data for the table without doing a full database restore

1. ApexSQL Recover offers a couple of options for recovering a table. The more information you have about the event that occurred, the easier it will be for you to get the table back using ApexSQL Recover

   I.e. if you know that the table was dropped with a DROP TABLE command or deleted using SSMS, you can use the option recover lost data From DROP TABLE operation. This allows ApexSQL Recover to find the data faster, since it only needs to look for dropped tables

   Let’s choose that option now

   

2. You will be required to connect to the SQL Server database from where the table was dropped

   

3. Provide the connection details

4. Click Next

   

   This screen allows you to add any database backups you may have. If you do not have backups available, ApexSQL Recover will only try to recover the data from the database that you are currently connected to

   Depending on how long ago the table was dropped, very little information may still be available in the SQL Server database itself. If you do have backups available you should add them here, this will not only speed up the recovery of your data but it will also improve the quality of the data retrieved

5. Select the Add transaction logs option

   

6. Add all the backups that you have available

7. Click Next

   If you know the approximate time that the event occurred, choose the relevant option here. Giving an approximate time will speed up the operation

8. Select the relevant option

9. Click Next

   

   ApexSQL Recover allows you to write the extracted data to a script file or to extract it into a new database

10. Select your recovery action

11. Specify the name of the SQL Server database to be created, if applicable

12. Click Next

    

    Here you can choose to recover either the structure and the data, or only the table structure

13. Select your recovery option

14. Click Recover

    

    A result screen will be displayed to show what tables were recovered

In conclusion

When something goes wrong and a table gets dropped or accidently truncated, you may not have enough time to restore an entire database. When this happens, ApexSQL Recover is the answer.

ApexSQL Recover can identify the data associated with a single table and extract only the relevant data from your native SQL Server backups, eliminating the need for a time consuming full backup restore.

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.