10

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?

Improve this question
5
  • 11
    See the answer here Commented Sep 20, 2012 at 15:54
  • There is no Ctrl+Z in SQL Server. Commented Sep 20, 2012 at 16:12
  • 7
    If it's any consolation, most of us have done something like this at one point in our careers. Note the advice given on one of the answers to the question Martin linked to- own up to the mistake ASAP Commented Sep 20, 2012 at 16:14
  • 1
    Agreed with @MartinWilson. In addition let me told you that I know some colleagues that prefer to start writing update statements intentionally malformed as: U.PDATE WhatEver SET X=Y; so that if they execute the query by mistake before they are sure the sentence is right, it is not executed... anyway you'll be still human and mistakes are part of our nature..., good luck and hope you can find a backup of your db ! Commented Sep 20, 2012 at 16:47
  • 2
    Thanks to all of you guys, good news we have got backups for the database. So everything is ok after all :) Commented Sep 20, 2012 at 16:53

1 Answer 1

Reset to default
8

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for you answer, actually it's ok we were making daily backups for the database, so we recovered the data and it's cool :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.