How to rollback changes in SQL Server

Question

Hi By mistake 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

Answer 1

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.

Answer 2

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.