Walkthrough: Comparing the Schemas of Two Databases

  • Article

In this walkthrough, you compare the schemas of two databases by using Team Edition for Database Professionals. The schema-comparison action also generates a Data Definition Language (DDL) script from the differences. Use this file to synchronize the schema of the target with that of the source. For more information, see Comparing Database Schemas.

You will follow these procedures in this walkthrough:

  • Create an empty database. The Northwind database serves as the source, and you create an empty database to serve as the target.

  • Compare the schemas of two databases. By comparing schemas, you find the structural differences between the databases, display the differences in a table, and generate a DDL script that expresses the differences.

  • Examine the synchronization script. You check the DDL script, and you can edit it before you run it.

  • Update the target database. You run the DDL script to change the schema of the target.

Prerequisites

You must have the following products installed:

  • Microsoft SQL Server 2000 or Microsoft SQL Server 2005

  • Microsoft Visual Studio 2005 with Team Edition for Database Professionals 

You must have two databases to compare. You start with the Northwind database as the source. You also create an empty database to serve as the target. This is described in the following procedure.

Create an Empty Database

Follow these steps to create an empty database.

To create an empty database

  1. On the File menu, point to New, and then click Project.

    The New Project dialog box appears.

  2. If you want to create a SQL Server 2000 database, click SQL Server 2000 Wizard. If you want to create a SQL Server 2005 database, click SQL Server 2005 Wizard.

  3. In Name, type EmptyNW.

  4. Accept the defaults for the remaining fields and then click OK.

    The New Database Project Wizard appears.

  5. Click Configure Build/Deploy.

  6. In Target connection, click the Browse button and specify the connection to the database server where you want to create the empty database.

  7. Click Finish.

    A database project named EmptyNW is created and appears in Solution Explorer.

  8. Click the EmptyNW database project in Solution Explorer.

  9. On the Build menu, click Deploy ProjectName.

    The database project is built and deployed to the specified server.

Compare the Schemas of Two Databases

To compare the schemas of two databases

  1. Open Visual Studio.

  2. On the Data menu, point to Schema Compare, and then click New Schema Comparison.

    The Schema Compare dialog box appears. Use it to specify the source and the target. In this dialog box, you identify the servers on which the source and the target reside, the name of each database, and the type of authentication to use when you connect to each database.

    Also, the Schema Compare window opens in the background, and Visual Studio automatically assigns it a name such as SchemaCompare1.

  3. Under Source database, in Server name, type the name of the server on which the Northwind database resides.

  4. In Select or enter a database name, type Northwind.

  5. Under Target database name, type the name of the server on which the EmptyNW database resides. This name should be the same server that you typed for the source.

  6. Under Select or enter a database name, type EmptyNW.

  7. Click Finish.

    The schema comparison starts.

    Note

    You can stop a schema comparison operation that is in progress by opening the Data menu, pointing to SchemaCompare, and clicking Stop Schema Compare.

    Note

    You can configure options that determine what is considered a difference and that change how the update script is created. For more information, see How to: Set Options for Comparing Database Schemas.

    When the comparison is finished, the structural differences between the two databases appear in the table in the Schema Compare window. The table displays a row for each database object that exists in either database. Database objects are organized by type: tables, views, stored procedures, roles, and so on.

Update the Target Database

To update the schema of the target, you have two choices. You can update the schema directly from the Schema Compare window or by using the T-SQL editor. This section describes both choices.

After you have run the schema comparison, the structural differences appear in the table in the Schema Compare window. For each object in the Northwind (Source DB) column, the table displays an action in the Update Action column that would be necessary to synchronize that object in the two databases. In this case, because the target is empty except for default objects, the Status column contains mostly New statuses and the Update Action column contains mostly Create actions.

Write Updates to Target

You can update the schema of the target using the update actions listed in the Schema Compare window. To perform this task, follow the steps in Write Updates to Target Database.

View Script, and then Write Updates to Target

You can export an update script, examine it, possibly change it, and then use it to synchronize the target database. To perform these tasks, follow the steps in Examine and Execute the Synchronization Script.

Write Updates to Target Database

To write updates to the target database

  1. (Optional but recommended) Back up your target database.

    Because some schema changes cannot be transacted, you might lose data if you start and then cancel an update operation. For example, a table in the target database may have been dropped in preparation for its re-creation. If you cancel the update at that moment, you could lose the table.

  2. On the Schema Compare toolbar, click Write Updates.

    The update actions that were listed in the Schema Compare window are executed. This synchronization changes the schema of the target to match that of the source.

    Note

    While the update action is occurring, you can cancel the operation by opening the Data menu, pointing to Schema Compare, and then clicking Stop Writing to Target.

    Note

    The comparison is not refreshed automatically. If you want to run the comparison again to verify that the selected updates were applied, you must click the Refresh button on the Schema Compare toolbar.

Examine and Execute the Synchronization Script

To examine the synchronization script

  1. On the Data menu, point to Schema Compare, point to Export to, and then click Editor. You can also click Export to Editor on the Schema Compare toolbar.

    The T-SQL editor opens in connected mode and displays the T-SQL synchronization script. This window has a name such as Server.Northwind - SchemaUpdate_EmptyNW_1.sql. It displays the T-SQL script. This is in a file in the My Documents/Visual Studio 2005/My Projects folder. Because you have both write and read access in this window, you can change the script. If you change it, open the File menu, and then click Save.

  2. To synchronize the schemas of the two databases, run this script by clicking Execute SQL on the Schema Compare toolbar, or pressing F5.

    Note

    The comparison is not refreshed automatically. If you want to run the comparison again to verify that the selected updates were applied, you must click the Refresh button on the Schema Compare toolbar.

Next Steps

Now, you can compare the data within the two databases. For more information, see How to: Compare the Data of Two Databases.

See Also

Tasks

How to: Compare the Data of Two Databases
How to: Compare the Schemas of Two Databases

Concepts

Terminology Overview of Team Edition for Database Professionals

Other Resources

Comparing Database Data