How to recover SQL Server data from accidental UPDATE and DELETE operations

The SQL DBA’s worst nightmare is the loss of data due to their own mistake. It’s not uncommon for someone to improperly execute or even forget a WHERE clause entirely when executing DELETE or UPDATE queries, resulting in potentially millions of rows of compromised data

So, which SQL Server data recovery options are available?

The solution could be using a backup which was made right before the disaster.

Common solutions to recover lost database data using the existing database backup are:

  1. Restore the database backup and use it instead of the original database
  2. Otherwise
    • Restore the backup on the same server as the original database using a different database name
    • Locate the relevant rows in the recovered database
    • Update the broken rows using the original rows from the recovered database

Unfortunately, rarely is the recovery scenario so “neat and clean” as to allow for such a simple solution.

More likely:

  • Mistakes were not detected early enough
  • There were subsequent data changes between the last database backup creation and the query execution which complicates the recovery significantly
  • Influx of new data
  • Database backups were not made regularly enough, or at all


Up the creek, WITH a paddle

Reading information from online transaction logs, transaction log backups or detached transaction logs, in order to recover database data, is one option to recover the data.
ApexSQL Log is a SQL Server recovery tool capable of reading transaction log data and recovering lost database data by rolling back transactions to their original state.

To recover lost database data due to accidental UPDATE or DELETE operations using ApexSQL Log:

    1. Start ApexSQL Log
    2. Connect to the database
    3. If you have any transaction log backups and/or detached transaction logs containing the needed data, click on Transaction logs in the left pane of the Project dialog window.
      • To add transaction log backups click Add Backups
      • To add detached transaction logs click Add Detached
      • Select the needed transaction log backups or detached transaction logs and click Open

    4. Use Filter to narrow down the resulting list to exactly what you need

    5. Adding a database backup can help increase the performance and quality of audit trail data.
      • Click Advanced and select Database backups from the left pane
      • Click Add file(s) and open the backup file(s)

 

  • After you have set all options, click Open

 

Your transactions will be listed in the application’s main grid. You can still use the Filter in the left pane to additionally eliminate unneeded transactions

To perform data recovery, check-in the transactions you want to roll back and click Create undo script in the Actions tab.

To execute the script from the script editor, click the Execute button or press F5 and the lost data will be restored into the database.

So remember, if you unintentionally used DELETE/UPDATE, you can still recover the lost database data, in many cases, with ApexSQL Log

Author Nikola Dimitrijevic