How to roll back without transaction

Question

I made a huge mistake, I executed this request:

update Contact set ContaPassword = '7FD736A3070CB9766' 

I forgot the WHERE , so it updated the password for all users .: (

Is there a way to recover data before this request?

Answer 1

You cannot undo a change if you run it outside of BEGIN TRANSACTION / ROLLBACK. This is why I begin any production data update:

 BEGIN TRANSACTION -- report the bad or undesired data condition before-hand SELECT ... -- change the data INSERT/UPDATE/DELETE ... -- ensure we changed a reasonable number of records; may not be accurate if table has triggers SELECT @@ROWCOUNT -- get the data condition afterwards and be sure it looks good. SELECT ... -- always start with this enabled first ROLLBACK -- don't do this until you are very sure the change looks good -- COMMIT 

Martin Smith noted this excellent post by Brent Ozar on dba.stackexchange.com on this topic. In full recovery mode, you can view the log files to see what has changed.

In addition, as Oded noted, if you have backups, it is easy to return to the original data. You can restore the backup somewhere and copy the original data.