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


We need to log who ran the query and what they ran
Ideally we need to give the person access to only run queries against the tables of interest and only for a short time
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
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?

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.

Whenever the situation allows, test the whole change within a transaction which is rolled back at the end, after the post-change validation has run. When confident with the results, change the rollback to a commit.

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.
