How to recover only individual rows from backups

“I’ve mistakenly deleted/updated important records in a SQL Server table. Is there any way to get these records back?”

This is a frequently asked question, and very often a headache for DBAs. If they are careful and make regular database backups, they can recover the lost database data even where the SIMPLE recovery database model is used

If that is the case, then database backups are the only solution to recover the lost database data. The deleted data has to be from the period prior to the database backup creation

So, how to recover the lost database data?

Restoring the database backup and using it instead of the original database would work fine if we didn’t have any data changes after the last database backup creation
Probably, the best solution would be to:

  1. Restore the backup on the same server as the original database using a different database name
  2. Locate the relevant rows in the recovered database
  3. Update the broken rows using the ones from the recovered database

SQL Server doesn’t offer “neat and easy” ways to do this. You can try such solutions as:

  • Using SSMS export/import wizard
  • Using MERGE statements
  • Writing direct queries to insert or update data
  • But they’re time-demanding, require a lot of work and above all, these processes are prone to errors. In such situations, the best choice would be the reliable and fast SQL comparison tool, capable of comparing and synchronizing selected database data

    ApexSQL Data Diff is an SQL comparison tool that allows comparing and synchronizing table or view data for both databases and database backups.

    Recovering of the individual rows from database backups can be performed in a few easy steps:

    1. Start ApexSQL Data Diff
    2. In the Project management dialog click New
    3. To select database backups as the data source:
      • In the source drop down menu, select Backup
      • Click Add file(s) to load database backups into the Backup sets list
      • Select the backup or backups that need to be used
    4. To select a database as the data destination
      • In the destination drop down menu, select Database
      • Connect to the SQL server
      • Select the database

      ApexSQL Data Diff offers useful options, including one of the most powerful – Object filter

    5. Click Show advanced tabs and select Object filter
    6. In the list of objects in the Project dialog, exclude individual objects from the comparison by deselecting them
    7. Additionally, it is possible to set up a more advanced, granular filter
      • Click Edit filter to choose the specific columns
      • Select columns to analyze and compare by selecting a specific object, and opening the drop down menu in the Columns.
      • Select those columns that need to be included in the analysis
    8. Click Compare

    Analyzed objects and differences results will be listed in the application’s results grid. It will show each compared object, along with basic information about the number of differences between the two objects. To select what should be synchronized:

    1. Choose the objects for synchronization
    2. Select individual rows for synchronization. Select those rows in the difference details pane, at the bottom of the main screen

    Once the project is set up and the objects for synchronization are selected, start the synchronization wizard. During the synchronization process, Output options offer you a choice whether to automatically run the synchronization script, open the generated script in an editor, or save the script to a file.

    The Summary and warnings step provides a detailed list of the actions that will be taken to synchronize the data sources, along with any potential problems that might occur during synchronization.

    After a successful synchronization, the recovery of the lost database data is completed.

    If you unintentionally deleted/updated important records and your database backups have the lost database data, then ApexSQL Data Diff is a reliable and fast solution, capable of solving this problem without disrupting your work.

    April 4, 2013