- Adam Thurgar
Update without a WHERE
It was a normal work day. Then all of a sudden, nobody could login to the web sites. This affected thousands of customers. There was an air of anxiety as everybody tried to figure out what had happened. It turned out that the mid level DBA had done a password update for a customer, but forgot the WHERE clause in the UPDATE statement. I am pretty sure that we have all done this either with an update or a delete. We were now in a situation where we needed to recover and quickly. We restored the last full backup to another server and applied the transaction logs to the time just before the update. This gave us the data that we needed to recover so we could then do a mass update. I had to take some of the blame. I should have checked the update before it was run, it should have been in an explicit transaction - where you could check the row count before committing. It also made us look at our processes. Direct access was severely restricted. Data changes need to be done through stored procedures., through an admin web site. Questions raised about whether we should dump out the contents of tables before any changes were made. It ended up being anything but a normal day and nobody got fired, we all make mistakes.