Creating migration scripts from the legacy model to the current one (being under development)

Question

Originally, the model (including tables, views, packages, etc.) was built from the DML scripts (generated by TOAD). The scripts look like:

DROP TABLE TABLESPACE.TABLENAME CASCADE CONSTRAINTS;
CREATE TABLE TABLESPACE.TABLENAME
...

Over time the model has changed - I've added new columns to the tables, altered some vews, added new methods to packages, etc.

What would be the easiest way (software, technique) to create migration scripts from the old model to the state of things I currently have. As a migration script I need to have a set of ALTERs, etc. that can be applied to the legacy model, so that it will be upgraded.

Answer 1

When you say that you need UPDATE statements, do you mean that you want the data to be kept updated to what you have in your new database? If you want the data to match exactly then you can do that, but if you want the same logic applied to the existing DB to bring it up to the new DB then you will need to have saved off those UPDATE scripts to be able to do that. For example, if you needed to increment the sales_date for all orders in your system because you found a bug in the code somewhere, there is no way to determine that from just looking at a database.

To make one database match another, Red Gate has some good tools - SQL Compare and SQL Data Compare should be able to help you. The data compare tool will only make the data match exactly and isn't very efficient for extremely large tables. It's good for things like look-up tables though.

There are other similar products out there (SQL Examiner comes to mind).

Answer 2

"Over time the model has changed - I've added new columns to the tables, altered some vews, added new methods to packages, etc.

What would be the easiest way (software, technique) to create migration scripts from the old model to the state of things"

Well, the easiest way of doing this would be to get all the DDL scripts you wrote to apply those changes from source control and run them against whatever database you need to.

I suppose the reason you're asking this question is because you haven't been doing things in that fashion. Tsk tsk.

The next easiest approach is to use the original scripts (I take ityou have those) and build a new schema (not the tablespaces obviously). Then compare your new schema against the modified schema to derive the differences.

If you have the DBA module for TOAD you can use its Schema Diff tool to generate a script which will apply all the necessary changes (if you dont have that requisite licence you can still use the Diff utility but you can't save the script as a file). There are other tools on the market which do this: most of them are either chargeable products in their own right (SQL Compare) or require additional licences (Oracle's free SQL Developer offers this functionality but you need to licence the Change Management Pack in order to use it).

If you have the time, a cheaper option is to generate your own scripts using the data dictionary to identify the changes. But that is a lot of effort.