You get a call. Someone has done something bad – in this case, failed to select the
WHERE clause when running an
UPDATE statement against your production database. Oops.
Leaving aside thoughts of “why is he messing with production data”, how do you get out of this?
Knee-jerk Response – RESTORE
The initial knee-jerk reaction –
RESTORE from the last good backup – is an option. However, it’s going to cost you data, publicity, image, etc. Is there a better way?
If you don’t know the structure of your data, then before you follow through on your initial panic-laden response, have a quick look at the table that’s been hit. You might be pleasantly surprised. I was. There’s a column called “LastModifiedDate” that is updated when an in-house application writes data to the table that was affected. And nothing had been officially updated for over an hour before the disaster, which meant that the most recent transaction logs would have been before the disaster struck.
My solution to this particular problem was obvious, given the above information.
- Restore to the last known good backup in the DR environment. Fortunately, I had a script that took just five seconds of editing to make sure it only restored the affected database
OPENROWSETto copy the appropriate data from the DR environment to a temporary table on the production environment
UPDATE...FROM...to correct the data in the production database
In this case, I was lucky – I knew that those steps were available to me, *but*, I’m embarrassed to admit that I wasn’t sure of the exact syntax. So here’s the learning from my perspective.
Basic OPENROWSET syntax:
SELECT foo.* FROM OPENROWSET('Provider', 'ConnectionString', 'Query' ) AS foo
UPDATE statement, as well as allowing for a basic UPDATE of a single table based on rules against that table, allows for you to update a table based on data in another table (or view, or JOIN, or…). Check the MSDN documentation for the UPDATE statement, and scroll down to where it says “Updating data based on data from other tables”. The basic syntax:
UPDATE TableToUpdate SET field = OtherTable.field FROM TableToUpdate t JOIN OtherTable OtherTable ON t.Key = OtherTable.Key WHERE...
OK, so it wasn’t necessarily the fastest fix I’ve ever done – the whole process took about 30 minutes. However, the examination of the data affected, and consideration of the options meant that no transactional data was lost in that situation, and that therefore the impact to the business was negligible.
In modern times, with modern software (eg Red-Gate’s Backup and Virtual Restore or Quest’s Litespeed), it might also have been possible to do this with an object-level RESTORE. Or, with RedGate, just by mounting the backup as if it were a database, and using that to do the UPDATE…FROM.
Prevention is better than cure
Of course, this sort of thing could have been prevented. Some options for this include:
- Not allowing access without going through applications. This isn’t necessarily helpful, as users will make mistakes that require correction via direct data manipulation
- Trigger to prevent mass updates on the table – more interesting, but a pain to implement across all tables within a database, and would cause performance problems for maintenance-style applications. I’ll put together some notes on this later.