Recover deleted SQL data from a backup or from online database files

Recovering DELETED records is something we would all like to avoid, but no matter how careful we are, it can come to that. Having regular full database and transaction log backups is a necessary practice and a great advantage in this situation, but even then, bringing back the deleted records might not be easy

The best-case scenario is that the records are deleted immediately after the last backup (full or transaction log one), no other changes happen, you can afford the database downtime and restore the backup. As this scenario almost never happens in real life, this kind of action will overwrite all the transactions that happen after the deletion of records, not to mention the downtime you will experience with the entire database

The first question you should answer is: do you have any database backups that contain the records you want to recover?
If the answer is yes, you can use SQL Server management Studio and the Export data option:

  1. Take the old database backup and restore it on SQL Server

    Restore database backup in SQL Server

  2. If you’re restoring it to the same SQL Server instance, make sure you specify a new name and path for the data and log files

    Adding a new name path for the data and log files

  3. In the restored database, find the missing records. This is easy if all of them have been deleted from a single table. But if the records were deleted from multiple tables, and also fulfill specific requirements, this can be quite challenging. This step is not necessary, but it will help you recover the records if you know what you are looking for
  4. Script the records. Right click the database in Object Explorer and select Tasks > Export Data

    Selecting Export Data in Object Explorer

  5. Specify the restored database as a data source and the original one as a destination

    Specify the restored database as a data source

    Specify the original database as a destination

  6. Click Next and you’ll get the following dialog

    Choosing table copy or query

    Although the option Write a query to specify the data to transfer looks more promising in a situation when the records from several tables are deleted using a specific query to filter them, it will not insert the selected records into their corresponding tables. All records will be inserted into a single table. This option doesn’t allow multiple result sets and only the first result set is inserted
    If you go for this option:

    1. Specify the Select statement that returns the deleted records

      Provide a source query

    2. Specify the destination table. By default, a new table dbo.Query is set, so you have to change it to match the source table

      Select source tables and views

    3. Click Edit Mappings. Here, you can:
      • Specify whether to drop and re-create the destination table. This option is disabled if a dependency will be broken
      • Specify whether to enable identity inserts in the destination table. This is recommended to avoid script failure due to inserting into a read-only column
      • Specify whether to truncate the existing destination table

        Column mappings

    4. Click OK and complete the wizard steps. After the records are inserted, you have to repeat the steps a. through d. for all tables with deleted records

      Message: The execution was successful

Another available option in SQL Server Management Studio Export data wizard is to copy data from one or more tables and views. You can specify several tables at once, but it’s not possible to filter the records

  1. Select the tables from which the records have been deleted

    Tables and views selection

  2. Check out the mapping for every table

    Check the mappings for every table

  3. Go through the wizard. After the records are inserted, if the destination tables are not empty, you might get an error report such as this one

    Operation stopped error

    Error messages report

Based on the examples shown above, the disadvantage of this method is that for multiple tables, it’s not easy to extract just the records you want to insert and to successfully insert them into the existing tables. The problems can be caused by violating primary and foreign keys, inserting duplicate data, etc. The tables with constraints cannot be dropped and recreated, so you cannot work around some of the problems this way

Use ApexSQL Data Diff

ApexSQL Data Diff is a SQL database comparison and synchronization tool which detects data differences. It can compare and synchronize live databases and native or natively compressed database backups and generate comprehensive reports on the detected differences

  1. Start ApexSQL Data Diff
  2. Select the database backup that contains the deleted records as a source
  3. Select the online database as a destination

    Choosing online database as a destination in ApexSQL Data Diff

  4. Click Compare
  5. The tables with different records are shown in the ApexSQL Data Diff main grid
  6. Select the tables you want to synchronize. The selection is available on a column level also, so if there are some records you don’t want to synchronize, just uncheck them

    Select the tables for synchronization

  7. Click Synchronize on the menu and go through the steps in the Synchronization wizard

    Selecting how to proccess the synchronization script

    Synchronization script impact preview

  8. When the script is created, execute it. All the missing records will be inserted into the live database, without any identity or key violation problems

    Batch data synchronization script

Use ApexSQL Recover

Another tool that can recover deleted records from a database backup is ApexSQL Recover, a SQL database recovery tool which recovers deleted, truncated, or dropped data. Besides reading the database backups, it reads online transaction log, transaction log backups and detached transaction logs.

To read the deleted records from a database backup using ApexSQL Recover:

  1. Start ApexSQL Recover
  2. Select the Recover lost data due to a DELETE operation option

    Recover lost data due to a DELETE option

  3. Select the database from which the records have been deleted

    Choose the database to recover

  4. Select the Add transaction logs option

    Several sources are available for the recovery option

  5. Besides the online transaction log, ApexSQL Recover automatically lists all data sources – transaction log backups and database backups created for this database. All of these will be analyzed. If you would like to add additional transaction log or database backups and a detached log file, click Add button.

    Specifying additional data sources for the recovery

  6. If possible, specify the time when the records were deleted

    Specify the time when data was lost

  7. Select all the tables or just the specific ones

    Select the tables to recover

  8. Change the path for the recovery script if you want and click Recover

    Save recovery script to file

  9. The created recovery script will insert the deleted data back into your database. Check it out and execute

    Recovery completion message

Even if you don’t have a database backup that contains the deleted records or a full chain of transaction log backups, ApexSQL Recover can help, as it can read the deleted transactions from the database online data file – the MDF file. When a record is deleted from a table, it’s not immediately deleted from the MDF file but just marked to indicate that the space where the record was stored is now available. After a while this space will be reutilized, but in the meantime the record can still be retrieved. That SQL Server’s behavior is used by ApexSQL Recover

  1. Start ApexSQL Recover
  2. Select the Recover lost data due to a DELETE operation option
  3. Select the database from which the records have been deleted
  4. Select the No additional transaction logs are available option

    Additional available data sources

  5. Select all the tables or just the specific ones
  6. Change the path for the recovery script if you want and click Recover
  7. The created recovery script will insert the deleted data back into your database. Check it out and execute

Deleted data recovery has the highest success rate when you have all data sources necessary for a successful recovery, such as a full database backup with the deleted data. In this situation, you can choose among several scenarios, but not all of them are quick and error-proof. ApexSQL Data Diff provides a reliable and user-friendly solution for more complex recoveries, while ApexSQL Recover automatically finds and analyzes all available backups created for the database and can recover deleted data from transaction log files (online, backup or detached). If you have none of the data sources mentioned above, your only choice is to scrape the database data file and ApexSQL Recover can help there, too.

June 4, 2013