2 of 2 people found this helpful
It's a common issue.
You basically have two big options:
1- make your development copy the new production copy and import all data from production
2- redo your changes in the production copy. If you do; you have to follow a very definite sequence to avoid breakage. We keep track of what changes we make in a 'tracker' file that then creates a report of all the changes in the right sequence. That takes away a lot of the stress during such a go-live
To some extent you can alleviate this problem by following the data-separation model where you keep your UI and scripts in one file and all data tables in another file. That allows you to swap out the UI file with the new one and just redo the minimal changes to the data file.
Some additional techniques:
importing the data from one file into another can be scripted. The script can combine multiple imports into different tables and also update next serial values. You can test such scripts on a clone of a backup to make sure it works before using it to update your production copy.
When copy/pasting small script changes into a working copy from a development copy, I document the change in a comment block at the beginning of the script with a name, what was changed, and a date. I then disable any code that is being removed or replaced, paste in the new code and encapsulate both with comments that read: "Begin Change Name Date" and "End Change Name Date".
This is so a change can quickly be rolled back if it causes a problem. We then have a practice of removing the begin/end comments and disabled code the next time we review the script if the changes are at least a month old.
Another option: make your solution in 2 files; one with the database only (backend) and another one with the front end. That works wondefully for me. If you need to make changes to the front end, just do it and (in your case) copy it to the production. If you need to change anything in the datase itself (backend) you have to ask everybody to stop using it while you make your changes anyway.
Thank you for your answers.
They're all interesting, especially the data-separation option as suggested by Wim and Reginaldo. Thank you too Philmodjunk. Commenting out the original script and then replacing it with a changed version was a trick I learned in this forum.
Gonna do some investigating of the data-separation model.