Unmitigated Disaster? Or was it also a Learning Experience?

The Situation

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.

Sensible answer

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
• use OPENROWSET to copy the appropriate data from the DR environment to a temporary table on the production environment
• use UPDATE...FROM... to correct the data in the production database

The Learning

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.

OPENROWSET

Reading the MSDN documentation for OPENROWSET, and the way that security works in that site, also reminded me of the ConnectionStrings website. I didn’t need this site… this time.

Basic OPENROWSET syntax:

SELECT foo.*
FROM OPENROWSET('Provider', 
                'ConnectionString',
                'Query'
               ) AS foo

UPDATE...FROM

The 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...

Summary

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.

Other Options

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.

About these ads