As a database administrator, you might need to frequently
migrate the changes that were made in one database environment to
another database environment. For example, you might need to promote
the changes that were made to a large development database to the
formal test database.
The following table shows the process of migrating changes from
one database environment to another.
Figure 1. The
process of migrating changes from one database environment to another
This task of migrating changes requires you to determine the differences
between the two database environments, and to change only those objects
that are different. In some cases, determining the differences might
be easy, such as when only a new table has been added. In other cases,
determining the differences might be much more complex, such as when
numerous changes are made to multiple tables, tables have been added
or dropped, primary or foreign keys have been changed, and so on.
You can use three different approaches to migrate changes from
one database to another:
- Copy and paste method
- Use the copy and paste method when you have already identified
the differences, are confident that those changes are the ones that
you want to migrate, and have no need to analyze the differences in
detail. When you use the copy and paste method, the Compare
Editor is not displayed. Instead, when you copy and paste
an object from one database to another in the Administration
Explorer or Object List, the Change
Management Script Editor is displayed, and the changes
are immediately migrated to the database that you want to change.
Important: The copy and paste method is the
only method that supports copying objects and data from one schema
to another schema in the same database.
Note: The
copy and paste method is also the only method that allows you to specify
whether you also want to copy the objects that are required by the
selected objects. By default, the other copy methods also copy the
required objects. For example, assume that Table A depends on Table
B and Table B depends on Table C. When you copy Table A and select
the Copy required objects check box, Table
B is also copied, but Table C is not copied.
- Drag and drop method
- Use the drag and drop method when you have already identified
the differences, are confident that those changes are the ones that
you want to migrate, do not need to analyze the differences in detail,
and want to move only the structural differences. With drag and drop,
data is not preserved. For example, if you migrate a table from one
database to another, the data in the table is not preserved when it
is migrated.
- Comparison method (Compare Editor)
- Using the comparison approach is recommended when the
changes are complex and you need to carefully determine the differences
between the two database environments. With the comparison approach, the Compare
Editor is displayed. A model of database that has the changes
that you want to migrate to the other database is shown on the left
side of the editor. A model of the database that you want to migrate
the changes to is shown on the right side of the editor. You can drill
down through the changes and choose only the changes that you want
to apply.
Important: You must use the comparison approach
if the source of the changes is a physical data model or a DDL script
file and not a database connection.
With any of these approaches, the objects to be changed are displayed
in the Change Management Script Editor, where
you can then generate, customize, and then run the change commands
to make the actual changes to the database.