How to rollback without transaction

Question

I made a huge mistake, I executed this query:

update Contact set ContaPassword = '7FD736A3070CB9766'

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

Is there a way I can recover the data before this query?

Answer 1

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

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 pointed out this excellent post by Brent Ozar on dba.stackexchange.com on this topic. In full recovery mode, it is possible to examine the log files to see what changed.

Also, as Oded pointed out, if you have backups, it is not hard to get back to the original data. You can restore the backup somewhere and copy the original data.