Tanzania Finance

Jul 31 2017

How to recover a single table from a SQL Server database backup – Solution center #restore #sql #server #database


#

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:

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

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:

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

If the table was completely dropped

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

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

  • Recreate any indexes, triggers, or constraints if this is required
  • Resolve any referential integrity issues

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

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

    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

    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

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

  • Provide the connection details
  • 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

    Select the Add transaction logs option

  • Add all the backups that you have available
  • If you know the approximate time that the event occurred, choose the relevant option here. Giving an approximate time will speed up the operation

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

  • Select your recovery action
  • Specify the name of the SQL Server database to be created. if applicable
  • Here you can choose to recover either the structure and the data, or only the table structure

  • Select your recovery option
  • A result screen will be displayed to show what tables were recovered

    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.

    Related posts:


  • Written by admin


    Leave a Reply

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