How to rollback changes in SQL Server

By mista开发者_如何学Goke I have updated data on production database. Is there any way to rollback those transactions. I have executed the update statement from management studio and the script does not have in Begin Trans/rollback/commit.

Thanks

Here is what I would do in this case:

1. Restore backup in separate database and compare these databases to recover rows that exist in backup?

2. If your database is in full recovery mode try reading transaction log to recover the remaining rows.

In order to read transaction log you can use a third party tool such as ApexSQL Log or try to do this yourself through fn_dblog function (here is an example but it’s quite complex).

Here are other posts on this topic:

Read the log file (*.LDF) in SQL Server 2008

How can I rollback an UPDATE query in SQL server 2005?

Without transaction (or indeed even with a committed transaction), there is no easy way to revert the changes made.

Transaction are mostly useful to ensure that a series of changes to the database are performed as a single unit, i.e. so that either all of these changes get performed [in the order prescribed] or that none of them get performed at all (or more precisely that the database server rolls-back whatever changes readily done would there be a problem before all changes are completed normaly).

Depending on the recovery model associated with your database, the SQL log file may be of help in one of two ways:

• If you have a backup and if the log file was started right after this backup, the logfile may help "roll forward" the database to the point that preceded the unfortunate changes mentioned in the question. (aka point-in-time restore)
• If no such backup is avaiable, the log file may be suitable to reverse the unfortunate changes

Both of these approaches imply that the SQL log was indeed maintained as some of the recovery models, are such that the log file get truncated (its data lost) after each successful batch/transaction. And neither of these approaches is easy, the latter in particular probably require third party software (or a lenghty procedure) etc.

Depending on how your backups are set up, you may be able to do a point in time restore. Talk to your DBA. You may also want to take the DB offline ASAP to prevent more changes that would eventually be lost when you do the restore.