none
How to rollback changes in SQL Server 2005

    Question

  • I have updated data on production SQL Server 2005 database by accident. I executed the update statement from management studio.

    Is there any way to rollback those transactions?
    Monday, August 05, 2013 12:59 PM

Answers

  • I have updated data on production SQL Server 2005 database by accident. I executed the update statement from management studio.

    Is there any way to rollback those transactions?

    Unless you have started an explicit transaction (BEGIN TRAN) and have not committed, the changes are permanent.  However, you might be able to get the original data back depending on your recovery plan.

    If you are running in the FULL recovery model, you can restore the database from you last full backup to a database of a different name on the same server.  Then apply differential backups (if applicable) followed by transaction log backups up to the point of the errant update statement using the STOPAT parameter of the RESTORE log command.  See the Books Online for details and examples of point-in-time recovery.

    Once the separate database has been restored, you can use that as the source to update the table with the pre-update changes.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, August 05, 2013 1:11 PM
  • First option for you is to restore a database backup in a separate database and compare these databases to recover rows that exist in backup

     

    Second option is to try reading transaction log to recover the remaining rows. You can do this only if your database is in full recovery model. In order to read transaction log you need to use a third party tools in this category.

     

    I found the article that can help you with this:

    http://stackoverflow.com/questions/721471/how-can-i-rollback-an-update-query-in-sql-server-2005

     

    Third option is to try reading transaction log on your own. This could be done using an undocumented fn_dblog function (here is an example but it’s quite complex).
    Monday, August 05, 2013 1:38 PM

All replies

  • I have updated data on production SQL Server 2005 database by accident. I executed the update statement from management studio.

    Is there any way to rollback those transactions?

    Unless you have started an explicit transaction (BEGIN TRAN) and have not committed, the changes are permanent.  However, you might be able to get the original data back depending on your recovery plan.

    If you are running in the FULL recovery model, you can restore the database from you last full backup to a database of a different name on the same server.  Then apply differential backups (if applicable) followed by transaction log backups up to the point of the errant update statement using the STOPAT parameter of the RESTORE log command.  See the Books Online for details and examples of point-in-time recovery.

    Once the separate database has been restored, you can use that as the source to update the table with the pre-update changes.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, August 05, 2013 1:11 PM
  • First option for you is to restore a database backup in a separate database and compare these databases to recover rows that exist in backup

     

    Second option is to try reading transaction log to recover the remaining rows. You can do this only if your database is in full recovery model. In order to read transaction log you need to use a third party tools in this category.

     

    I found the article that can help you with this:

    http://stackoverflow.com/questions/721471/how-can-i-rollback-an-update-query-in-sql-server-2005

     

    Third option is to try reading transaction log on your own. This could be done using an undocumented fn_dblog function (here is an example but it’s quite complex).
    Monday, August 05, 2013 1:38 PM