|
I've two databases consider as OldVersion and NewVersion. What I would like to do is that I need to compare the database for DDL changes (schema changes such as column difference, datatype difference, table difference). I would like to compare and generate a script such a way that it will upgrade the OldVersion database same as NewVersion database.
This is not a straight way to do that. Say for example, if a column is FK, that column could not be altered. This is one constraint. Likewise what are the possible constraints to generate the script? How could I resolve them?
To generate the script for entire database in what order I've to work on. I look for answer such as Drop Index, Drop Constraints, Drop Columns or Alter Column, Alter Index, Add Contraints in particular order.
I know I've some tools in place for that. But I would like to generate it.
The question is lengthier one. Please bear with me and provide your solution.
|
|
|
|
|
We use Red-Gate for this and it uses the dependency relationships to generate the scrip. However on a large change it can screw up and needs to be broken down, I do the tables, then the views and then the procedures.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You mean that you suggesting to use the tool and not to suggest to write script for it?
|
|
|
|
|
Sorry I was not very clear, I use the tool and the generated script. IF it fails (it automatically rolls back the transaction) I then use a subset of the objects, starting with the tables. I repeat this till I get a successful merge. I then save that script and get the next subset, repeating until I get all the changes across to the target database.
At that point I have a set of scripts that can be run sequentially to successfully update the target database. This is a pain but it is better than hand coding which is how I used to do it!
Never underestimate the power of human stupidity
RAH
|
|
|
|