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 the tool and connect to the database
2. In the select data sources step of the wizard, add the transaction log backups and detached transaction logs you want to read. Note that they have to form a full chain in order to provide successful auditing. Adding a database backup can help increase the performance and quality of audit trail data

   

3. In the next step of the wizard, opt for the Open results in grid option

   

4. Use the Filter setup options to narrow down the result set using the time, operation type, table’s name, user and other filtering options

   

5. After you have set all options, click Finish

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 Undo 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 or ApexSQL Recover