Overview of migrating changes from one database environment to another

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
The DBA examines the Development database to determine how it differs from the Test database. The DBA then applies the changes that were found to the Test database.

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.


Feedback