Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – Modifying Production Data Good Practise

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)

Comments

Leave a comment on the original post [www.sqlserver-dba.com, opens in a new window]

Loading comments...