Q. “I need to modify Production Data directly on a Production database. What are some steps I can follow to ensure rollback is possible? Also, some steps to ensure I can capture mistakes before the data becomes invalid “
A. Here are some suggestions
1. Take a Log Backup before the change – giving you a restore point
2. Sit with another DBA . They can verify what you’re doing is correct. This is particuarly useful if late at night
3. Create a table backup , before beginning.
4. Create a transaction. Confirm the results and record counts before you commit the changes. If it’s part of larger process consider some “human validation”. Gives you a chance to rollback if necessary.
5.If the Update source is from another database or a different database platform , such as DB2 , create a staging database. Place data in the staging database, validate the data and then commit the UPDATE
6. If using audit columns, set the updated datetime. Use unique characters to indicate the rows updated in this particular task.
Author: Jack Vamvas (http://www.sqlserver-dba.com)