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.  

See Also

BACKUP failed to complete the command BACKUP LOG

Disable Index and Rebuild Index for Updates on very large tables

Author: Jack Vamvas (http://www.sqlserver-dba.com)