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

Both ApexSQL Log and ApexSQL Recover are tools that can mine the database transaction log files 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 or ApexSQL Recover:

1. Start the tool and connect to database

   

2. In the Select data sources step of the wizard choose to use online transaction log file and add applicable transaction log backup files or detached ldf files, depending on the use case.

3. Next, choose the Open results in grid option

4. In the Filter setup use the Time range section to narrow down the recovery to the time when the UPDATE statement was executed

   

5. In the Operations tab, check Update operation and ensure that all other DML and DDL operations are unchecked

   

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

   

7. If possible, use the Users and Applications filters to narrow down the results

8. Click Finish

9. To create the Undo script:

   1. Check specific update operations in the grid
   2. Click Undo on the Home tab in the Actions group to create the undo script for all transactions shown in the grid

      

10. 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 or ApexSQL Recover