A major difficulty in working with databases is that changes come from two sides: data changes occurring in the production environment, and structure changes following the "normal" process (development, testing, quality, production - or whatever the progression of environments you/your business uses). Both have to occur in harmony.
Data Changes
There is little reason to change the data, but if necessary it can be done simply by making a dump or backup of the same, and versioning these copies the way you think best (maybe even git itself). It is important, of course, that when restoring these copies the structure of the bank is as it was when the copy was made, but this becomes simple when keeping a record of the structure changes in the BD itself (more on this later).
Changes in Structure
Irreversibility
Whatever method is used to change the BD structure (execution of an sql script, installation of external components), this change can only be made once, and it cannot be undone. Of course, you can run the same script twice and the end result is the same, or you can run a script that brings the BD to an identical state, but still it counts as two changes (especially if something goes wrong).
Given the irreversibility of structural changes, it is important to keep a record of which changes have already been applied to a specific BD, and if a given change has already been made, not to make it again. One way is to keep in the BD itself a table listing which changes were made, and in which order.
Testing, testing, testing, testing...
If a change made, say, in the development environment has failed in the testing environment, you should not simply change the testing environment and move on. On the contrary, both environments should be restored to the pre-change state (although this means destroying the BD and restoring it from a backup) and create a new change to replace the one that did not work (discarding it). If this time it worked in tests but failed in quality, it starts all over again...
This ensures that once the changes are ready to be applied to the production environment, they have already been tested multiple times in different circumstances, with different pre-existing data sets.
(and, responding directly to your particular case, I do not think it is a good idea to copy the status of the homologation environment for production - a conclusion you yourself have also drawn; the ideal would be to apply the same actions - script execution, component installation - performed in an environment in the next environment in the same order, naturally preceded by a complete backup of the bank)
Data Migration
Often data needs to be adjusted as a result of a change - setting default values for a newly created column, moving data and a table to another, converting from one format to another, etc. As this is something that affects all BD data - whatever state they’re in - and it’s only done once, you can treat that kind of change as a normal structure change - by versioning it and testing it from environment to environment.
This can be done in a single sql script or depending on the particularities of your tool (the Django-South for example differentiate between schema migration and data migration), in multiple steps. Example:
- Create a new column nullable, and mark an existing one also as nullable;
- Move all data from the old column to the new one, converting the format if necessary;
- Optional: test your application layer in this state;
- Mark new column as no-nullable, and destroy the ancient column.
Restoring to a Previous State
Finally, if for any reason it is necessary to restore the archived data in the past, all you need to do is:
- Verify in your copy what was the state of the bank at the time it was made (simple, because the set of changes that the BD suffered were registered in a table, and this was saved along with the other data);
- Check whether or not the current database is in the same state (i.e. same set of changes, same order); if yes, simply restore the data.
- If the bank is in a state previous, just perform the pending changes on it until it reaches the appropriate state to restore the copy.
- If, on the other hand, he’s in a state posterior, then it is necessary to put this data in a new provisional database, apply the necessary changes to bring it to the desired state, and then transfer the data to the definitive database.
Needless to say, the application code to be used should be in the same version which supports the bank in its desired state. Assuming you have saved the change scripts in version control, this consists of checking which latest stable version contains that set of changes, and no newer.
Note: this response was partly based on the article (in English) "Database Changes Done Right" and how the schematic migration tool works Django-South, beyond my personal experience.
Did you ever evaluate the Oracle SQL Developer 4.0 ? It accesses Mysql and has a versioning feature for GIT. But since I’ve never tested its versioning feature, I don’t know if it’s limited to Oracle and/or PL/SQL code only...
– ceinmart