Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Bugs happen and sometimes data has to be fixed in production. What is the safest way to go about this from a big company standpoint? Are there tools that can help? Here are some considerations driving this requirement...

  1. We need to log who ran the query and what they ran
  2. Ideally we need to give the person access to only run queries against the tables of interest and only for a short time
  3. Whatever is running the queries needs to have some smarts about it to not allow long running and locking SQL to run without explicit permission
  4. This process needs to be DB agnostic or at least understand DB2, Oracle, and SQL Server.

We are trying to reduce the risk of ad-hoc prod fix-up queries from doing the "wrong thing" and at the same time add some security/audtis to the process. Thoughts or Ideas?

share|improve this question
23  
Never let management think this is Standard Operating Procedure. This is emergency open heart surgery without masks or gloves, NOT a normal way of dealing with bugs that should have been caught in testing. –  Dan Pichelman Aug 12 '13 at 13:54
2  
It's because you want to work this way that the bugs happened in the first place. –  Mathew Foscarini Aug 12 '13 at 19:28
5  
@MathewFoscarini that comment adds nothing to the conversation nor clarifies anything. It is also wrong in that I never said I wanted things to work this way, only that we have some considerations that must take place. Some of the answers below address all of my points well. –  Andrew White Aug 12 '13 at 20:39
1  
@AndrewWhite my apologies Andrew no offense was intended. –  Mathew Foscarini Aug 12 '13 at 23:18

7 Answers 7

up vote 40 down vote accepted

Never ever update production databases manually.

Write scripts.

Triple check them, and have multiple people do that, not just a single person doing it three times.

Include post-change validation queries in those scripts.

Test those scripts ad nauseam against a test database.

Make a backup prior to running the script against the production database.

Run the scripts.

Check, validate and triple check the changed data using the post-change-validation scripts.

Do a visual check anyway.

If anything seems off, back off and restore the backup.

Do not proceed with the changed data as the production data until you are absolutely sure that everything is ok and you have sign off from the (business) managers involved.

share|improve this answer
18  
@Andrew that is no excuse: forget one WHERE and your database will be down for the rest of the day. Or week. –  CodeCaster Aug 12 '13 at 13:03
8  
@AndrewWhite You did ask for the safest way to fix the data, not the fastest. :-) –  Eric King Aug 12 '13 at 16:06
7  
@AndrewWhite - you've already got one problem. If you rush the fix, then you're going to have TWO problems, if not more, and/or you might make the problems WORSE, instead of better. –  Michael Kohne Aug 12 '13 at 17:16
4  
@AndrewWhite - frankly, having it be a non-trivial process would seem to be a plus to me. Everyone will be aware of the cost and risk as opposed to the "well, we've done it 23 times before without problems" blase-ness I've seen in a number of places. –  DaveE Aug 12 '13 at 18:33
1  
@EricKing: xkcd.com/349 –  Robin Aug 15 '13 at 20:46

The answer by Marjan Venema is technically valid and should be followed when possible. Alas, Marjan answers from the point of view of a theorist, or a purist database administrator who likes to make things cleanly. In practice, sometimes business constraints make it impossible to do things in a clean way.

Imagine the following case:

  1. There is a bug in the software product which causes it to stop working when it detects what it thinks being some data inconsistency in the database,

  2. All developers who could potentially fix the bug in the application are unreachable,

  3. The company is currently losing thousands of dollars per hour (let's say $6 000, which means $100 per minute),

  4. The bug is affecting several tables, one of which is huge, and concerns only the data itself, not the schema,

  5. In order to circumvent the bug, you should experiment a bit with the data, which involves both removing and changing it,

  6. The database is large and it would take three hours to take or restore the backup,

  7. The last full backup was taken three weeks ago; there are also daily incremental backups, and the last daily incremental backup was done 14 hours ago,

  8. Database backups are assumed reliable; they were severely tested, including recently,

  9. Losing 14 hours of data is not acceptable, but the loss of one to two hours of data is,

  10. The staging environment was lastly used six months ago; it seems it is not up to date, and it may take hours setting it up,

  11. The database is Microsoft SQL Server 2008 Enterprise.

The clean way to do things is to:

  1. Restore the backup in staging environment,

  2. Experiment there,

  3. Check the final script twice,

  4. Run the script on the production server.

Just the first step will cost $18 000 to your company. The risk is pretty low if you do the third step flawlessly, but since you work under extreme pressure, the risk would be much higher. You may end up with a script which worked perfectly well in staging, then screws the production database up.

Instead, you could have done like this:

  1. Create a snapshot (Microsoft SQL Server supports that, and it takes seconds to revert (and nothing to create) a snapshot of a database which takes an hour to backup; I imagine that other database products support snapshots as well),

  2. Experiment directly on the production database, reverting to the snapshot if something goes wrong.

While a purist would fix the database in a clean way and still have a risk to screw things up given the time pressure while wasting more than $20 000 of his company, a database administrator who takes in account business constraints will fix the database in a way which will minimize the risks (thanks to snapshots) while doing it quickly.

Conclusion

I'm a purist myself, and I hate doing things in a non-clean way. As a developer, I refactor the code I modify, I comment the difficult parts which couldn't be refactored, I unit-test the codebase and I do code reviews. But I also take in consideration the circumstances where either you do things cleanly and the next day you're fired, or you minimize both the risks and the financial impact by doing a quick hack which works.

If some IT guy wants doing things cleanly just for the sake of cleanness while it causes thousands of dollars of loss for the company, this IT guy have a deep misunderstanding of his job.

share|improve this answer
2  
And do your work off business hours if possible - when real customer activity is at a minimum –  Dan Pichelman Aug 12 '13 at 13:52
2  
Even if your database is large and backing it up takes a lot of time, you can probably just take a subset of that data and experiment on that. –  Radu Murzea Aug 12 '13 at 15:17
1  
An upvote for your edit, but: if the data is that crucial and costly to the business, it is absolutely idiotic that the operational procedures are in such utterly bad shape. No reliable backups, no environment minmicing the production environment, requiring experimenting with live data: I would definitely not want to work in such a stressful and unprofessional company. –  CodeCaster Aug 14 '13 at 8:58
3  
@CodeCaster: it's sad, but I often see this in practice, including in large companies. –  MainMa Aug 14 '13 at 9:09
2  
Most likely, the business got into this predicament precisely because they didn't follow the advice in Marjan's post when they had a chance. –  Eric King Aug 15 '13 at 22:51

Safely fixing production database data. What is the safest way to go about this from a big company standpoint? Are there tools that can help?

It is a bad practice and an invitation gate for more data problems and issues. There is even a phrase that describes this approach as "Quick and Dirty".

Continuing fixes/updates directly on a production server is very dangerous, as it will cost you/your company a fortune (law suits, bad/dirty data, lost businesses, etc.)

However, bugs will be there and need to be fixed. The de-facto industrial standard is to apply patches / (deployment scripts) on a Staging (pre-production environment with latest copy of prod database) and let data analyst/ QA to verify the fix. Same script should be version controlled and applied to the Prod environment to avoid issues.

There are number of good practices mentioned in this related post - Staging database good practices

Good set of references to look are:

share|improve this answer

In most organisation's I have worked updating data in the live environment was always done by a small group of people with the access rights to do so, typically with a job title such as DBA. As updates could only be done by the small number of people there's at least a chance that they gain familiarity with the data and therefore reduces (but not eliminates) the risk of problems.

The person writing the update script would do so in test (as per other answers) and get serious sign off from non-techies (those who know the system, plus someone with senior authority) that the features appear to be 'right again' in addition to their own paranoid testing. The scripts, and the data, would be independently verified by another techie (often the DBA role I mentioned) on test before being ran into production. The results would be checked against the anticipated values (unique for every scenario, but often things like rowcounts etc.)

In one company I worked for, taking backups wasn't a realistic option, but all rows to be updated were written off to a text file for reference BEFORE the update, and then again AFTER the update should anyone ever need to refer to it. The scripts and this data kept in a properly organised Data Change Log.

Every business is unique, and the risks on updating some data is clearly greater than in others.

By having a process that makes people have to jump through hoops to do these updates, hopefully you promote a culture that makes people want to treat this as a last resort, and create a healthy "double check, triple check" attitude around this stuff.

share|improve this answer
    
Oh and of course wherever possible analyse the code in the application to ensure any dependant updates hidden in the logic are catered for... And if there's any chance there are triggers on the tables you're updating check for them and have a think about whether they need disabling or not. –  Greystone28 Aug 12 '13 at 16:39

There are times when you must fix data on Prod that doesn't exist on other servers. This isn't just from bugs but could be from an import of data from a file that a client sent that was incorrect or from a problem caused by someone hacking into your system. Or from a problem caused by bad data entry. If your database is large or time critical, you may not have the time to restore the latest backup and fix on dev.

Your first defense (and something no Enterprise database can afford to be without!) are audit tables. You can use them to back out bad data changes. Further, you can write scripts to return data to the previous state and test them on other servers long before you need to revert audited data. Then the only risk is that you identified the correct records to revert.

Next all scripts to change data on production should include the following:

They should be in explicit transactions and have a TRY Catch block.

They should have a test mode that you can use to rollback the changes after you see what they would have been. You should have a select statment from before the change was made and one run after the change to ensure the change was correct. The script should make sure the number of rows processed is shown. We have some of this pre-set up in a template which makes sure the pieces get done. Templates for changes, help save time in writing the fix too.

If there is a large amount of data to change or update, then consider writing the script to run in batches with commits for each batch. You do not want to lock the whole system up while you fix a million records. If you have large anmounts of data to fix, make sure a dba or someone who is used to performance tuning reviews the script prior to running and run during off hours if at all possible.

Next all scripts to change anything on production are code reviewed and put into source control. All of them - without exception.

Finally devs should not run these scripts. They should be run by dbas or a configuration management group. If you have neither of those, then only people who are tech leads or higher should have the rights to run things on prod. The fewer people running things on prod, the easier it is to track down a problem. Scripts should be written so that they are simply run, no highlighting parts and running one step at a time. It is the highlighting stuff that often gets people in trouble when they forgot to highlight the where clause.

share|improve this answer

I have updated data many times in running production databases. I agree with the answer above, that this would never be standard operating procedure.

It would also be expensive (we would look over eachothers shoulders and discuss 2 or 3 maybe)

And the golden rule: always make a select statement to show what would be done before doing an update/delete/insert statement

The golden rule being enforced by the other two people in the team!

share|improve this answer

re: MainMa's answer...

There is a bug in the software product which causes it to stop working when it detects what it thinks being some data inconsistency in the database,

  • How do you know it's a "bug"? The data is inconsistent according to the rules the software product developer laid out.

All developers who could potentially fix the bug in the application are unreachable,

The company is currently losing thousands of dollars per hour (let's say $6 000, which means $100 per minute),

  • Apparently a loss of $100/minute is not important enough to the company management for them to locate and insure that competent developers return to fix their mistake and help you restore the database.

The bug is affecting several tables, one of which is huge, and concerns only the data itself, not the schema,

  • All database problems "concern" the schema. How the schema is designed is what is going to determine how you solve this problem.

In order to circumvent the bug, you should experiment a bit with the data, which involves both removing and changing it,

  • That's what your staging database is for. You may need to repopulate it with "corrupted" data from the production database right after you take a full online backup of production.

The database is large and it would take three hours to take or restore the backup,

  • Then you better get that started right away so it can run while you're analyzing the problem, developing your correction scripts, testing and refining them along with the developers and other DBAs helping you.

The last full backup was taken three weeks ago; there are also daily incremental backups, and the last daily incremental backup was done 14 hours ago,

  • You don't have at least daily full online backups? You're screwed. But you're probably used to that. Good thing that full backup you started above is running. Be sure management tracts every minute of the costs that could have been avoided with daily online backups.

Database backups are assumed reliable; they were severely tested, including recently,

  • Excellent! Then you might not have to restore the database more than once.

Losing 14 hours of data is not acceptable, but the loss of one to two hours of data is,

  • Under the scenario you've described, all bets are off. This is an "information disaster management" situation. A good thing for management to be doing throughout this is documenting the costs that could be avoided in the future with prpoer backups and recovery procedures and resources.

The staging environment was lastly used six months ago; it seems it is not up to date, and it may take hours setting it up,

  • If your backup system supports online backups (i.e. database fully operational during the backup), then you can do the extract to repopulate the staging database at the same time if you have sufficient hardware resources to avoiding slowing down the backup.

The database is Microsoft SQL Server 2008 Enterprise.

  • Harder to do all this but not impossible. Good Luck!
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.