Walkthrough: Comparing the Schemas of Two Databases

Article
08/23/2010

In this walkthrough, you compare the schemas of two databases by using Visual Studio Team System Database Edition. 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 Compare and Synchronize 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
Database Edition

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

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

The New Project dialog box appears.
Expand the Database Projects node.
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.
In Name, type EmptyNW.
Accept the defaults for the remaining fields and then click OK.

The New Database Project Wizard appears.
Click Configure Build/Deploy.
In Target connection, click the Browse button and specify the connection to the database server where you want to create the empty database.
Click Finish.

A database project named EmptyNW is created and appears in Solution Explorer.
Right-click the EmptyNW database project in Solution Explorer, and click Properties.
Click the Deploy tab.
In the Deploy action list, click Create a deployment script (.sql) and deploy to the database.
On the File menu, click Save All.
Click the EmptyNW database project in Solution Explorer.
On the Build menu, click DeployProjectName.

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

Compare the Schemas of Two Databases

To compare the schemas of two databases

Open Database Edition.
On the Data menu, point to Schema Compare, and then click New Schema Comparison.

The New SchemaComparison dialog box appears so that you can 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 Database Edition automatically assigns it a name such as SchemaCompare1.
Under Source Schema, click Database, and then click the connection that corresponds to the Northwind database.

If no such connection appears in the list, click New Connection. After the Connection Properties dialog box appears, identify the server on which the source or target resides and the type of authentication to use when you connect to it. Optionally, click a database on that server. When you are finished, click OK.

Note

After you establish a connection, it appears in Server Explorer under Data Connections.
Under Target Schema, click Database, and then click the connection that corresponds to the EmptyNW database.

If no such connection appears in the list, click New Connection. After the Connection Properties dialog box appears, identify the server on which the source or target resides and the type of authentication to use when you connect to it. Optionally, click a database on that server. When you are finished, click OK.
(optional) Click Options to specify which objects are compared, what types of differences are ignored, and what the generated update script will contain.

Note

When you compare databases, you do not specify SQLCMD variables.
Click OK.

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.

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

(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.
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.

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

Examine and Execute the Synchronization Script

To examine the synchronization script

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. 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. When you save the file, you can specify its path and name.
To synchronize the schemas of the two databases, run this script by clicking Execute SQL on the T-SQL Editor toolbar or by pressing F5.

The Connect to Database dialog box appears.
Click the connection that corresponds to the EmptyNW database, and click OK.

Warning

If you attempt to run the update script against a different database, you might cause results that you did not intend.

The comparison is not refreshed automatically. If you want to run the comparison again to verify that the selected updates were applied, you must select your schema compare session and then 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.