How to recover SQL Server data from accidental updates without backups

Ooops! Say you’ve performed an UPDATE statement without a WHERE condition against the database and overwritten a bunch of rows with bad data. How can you bring the right values back?

If you had a previously created backup that contains the original table values, you could easily recover the data. The most common solutions are:

  • Restore the database backup and use it instead of the original database
  • In case some other changes occurred after the UPDATE or you cannot allow the database to be offline:
    1. Restore a database backup on a test server
    2. Use SQL Server Management Studio Export data wizard to export the data
    3. Import the exported data into the original database

But, if you don’t have a full database backup, the UPDATEs can still be rolled back using the transaction information stored in the database online transaction log

The transaction log keeps the information about the transactions made to your database. If it hasn’t been shrunk or truncated, you can use ApexSQL Log

ApexSQL Log is a tool that can mine the database transaction log and rollback the accidental UPDATEs. So instead of having all the data in the table updated, you can roll back the UPDATE statement and then update only the data that fulfill a certain criteria

To recover data from accidental UPDATEs using ApexSQL Log:

  1. Start ApexSQL Log

  2. Connect to the database

  3. Use the Time range section in Filter setup options step to narrow down the recovery to the time when the UPDATE statement was executed/p>

  4. Select Update row in the Filter setup options step, Operations tab

  5. Select the table(s) where the data was updated in the Filter setup options step, Tables filter

  6. If possible, use the Users and Applications filters to narrow down the results
  7. Click Open
  8. To create the Undo script:

    1. Click Create undo script on the Home tab in the Actions group to create the Undo script for all transactions shown in the grid

    2. Check the transactions you want to create the Undo script for, right click in the Main grid and click Create undo script in the context menu to create the Undo script only for the selected transactions
  9. Execute the script against the database

After the Undo script is executed, the UPDATEs are rolled back and the original data is back in the database before it was damaged

If you unintentionally UPDATE database records and you don’t have a full database backup, there is a chance to recover it using ApexSQL Log

April 4, 2013