Overview of Comparing Database Schemas

You can use Team Edition for Database Professionals to compare the schemas of two databases and then decide whether to apply some or all schema objects from the source to the target. For one example scenario, see Why Compare Database Schemas?

You can compare the schemas of the following:

• two databases, which includes two versions of the same database

• a Visual Studio database project and a database

When you configure the comparison, you use the Schema Compare dialog box to designate the source and the target. For more information, see How to: Compare the Schemas of Two Databases.

After schema comparison finishes, it displays the comparison results for you to view. For more information, see How to: View Schema Differences. You can then propagate schema elements from the source to the target. For more information, see How to: Synchronize Database Schemas.

Why Compare Database Schemas?

The following story depicts one scenario in which you would want to compare database schemas. In this case, you compare one database and a database project, instead of two databases.

Team Database Development

You are a database developer who is using a Visual Studio database project to help create a database. Over time, your team builds successive versions of the database and deploys them to testing servers. Other members of your team also contribute to the design of the database.

You have just finished making a round of changes to the database schema and tested the changes on your own computer. Because you found no errors, you now want to publish the updates to a testing server so that your team's quality-assurance staff can test them.

You open your database project and, by using the New Schema Comparison dialog box, you establish a connection to the testing server. You compare your project (the source) to the deployed database (the target) and determine that many objects differ between the two entities. But you also see that most of the differences are in objects for which you are not responsible. Other people made those changes, and you do not want to overwrite them.

Now, you select just the objects that you have worked on, and you click Write Updates. This action updates your part of the database and leaves the other parts as they were. Your work is now deployed and ready to be tested.

The following section describes how Team Edition for Database Professionals shows you what schema objects have changed and helps you decide what actions to take.

Controlling Schema Comparison

You can specify options that control the schema-comparison process and the script-generation process at two levels. To set options globally, open the Tools menu, click Options, expand Database Tools, and then click Schema Compare. Options that you set globally affect all subsequent schema-comparison operations unless you override them for a specific comparison.

Schema Compare Views

After you compare two schemas, the Schema Compare window displays results in a grid. The window also displays information about objects in the databases, including the script to synchronize them.

• Understanding Schema Compare Results

• Viewing Object Definitions

• Previewing the Update Script

Understanding Schema Compare Results

The Schema Compare window occupies the main editing area of Visual Studio. The window displays all the objects in both schemas, organized into expandable folders. These objects include tables, views, stored procedures, functions, user-defined types, rules, defaults, full text catalogs, users, and roles.

In this window, the columns display the following information about each object:

• The first column, Status, shows the comparison state of the compared objects:

  • Equal. The source and target objects are structurally equal.

  • Different. The target and source objects are structurally different.

  • Missing. The object exists in the target but not in the source.

  • New. The object exists in the source but not in the target.

  Note

  Objects are judged to be equal or different in the context of any schema-comparison options that you have set. For more information, see Controlling Schema Comparison.

• The second column lists the names of schema objects in the source, organized hierarchically by object type. In this column, you can expand and collapse nodes to show only the object types that you want to see.

• The third column, Update Action, shows the action that, for a particular object, would synchronize the structure of that object between the two schemas. (Changes are written only to the target.) Values in the Update Action column correspond to values in the Status column. For every object, you can choose the Skip action, which leaves the object in the target as it was before the comparison. You might not be able to Skip an update if the Include Dependencies check box is selected. (To verify the status of this check box, open the Tools menu, and click Options.) For example, if the source database contained a new table with indexes, keys, and triggers, if you add the indexes, keys, and triggers, you cannot skip the table.

  Note

  Changing values in the Update Action column actually changes the update script that will be run. In other words, it changes the results that would appear when you click Write Updates. For more information, see How to: Synchronize Database Schemas.

  The following table summarizes these actions:

• The fourth column lists the names of schema objects in the target.

Viewing Object Definitions

You can also view the SQL definition of each database object that is listed in the Schema Compare window. When you click the object's row in the main document window, its definition appears in the Object Definitions pane, which is read-only. The left side of the Object Definitions pane displays the definition of a selected object in the source, and the right side displays the definition of a selected object in the target. You can scroll through both definitions simultaneously by using the scroll bar on either side. Lines that differ between the source and target object definitions are highlighted.

Object definitions reflect the status of the object at the time that you ran the comparison. They do not change when you change values in the Update Action column or if the object undergoes changes outside this schema-comparison session.

Previewing the Update Script

Comparing schemas also automatically generates a synchronization script and displays it in the Schema Update Script window. This Data Definition Language (DDL) script would be used to update the target. The script contains only objects whose comparison state is Update, Drop, or Create. Items to be skipped do not appear in the Schema Update Script window.

When you change values in the Update Action column, the Schema Update Script window reflects your changes.

Although you can change the DDL script by changing update actions, you cannot edit the script directly in this window because the Script Preview pane is read-only. If you want to edit the update script, click Export to Editor, and then modify the update script in the Transact-SQL (T-SQL) editor.

Refreshing Comparison Results

You can update your view of the differences between the source and target schema. After you have compared the source and the target once, you might want to refresh the view if either or both of the schemas changes. You might also want to refresh the results if you change the schema compare options. When you click Refresh, the configuration information that you indicated previously is reused to generate new comparison data.

Database objects may have been added to or deleted from the source since the most recent schema comparison. In this case, when you click Refresh, Schema Compare attempts to include new objects in and exclude deleted objects from the new comparison, as appropriate. However, if you have changed the Update Action setting for a particular object, that setting is preserved regardless of any changes to the object that have occurred since the most recent schema comparison.

Propagating Database Schemas Safely

Propagating database schemas can cause data loss. To avoid it, you should pay close attention to the names of objects in your source and your target, especially just before you update the schema in the target.

For example, your target contains a table named Order_Details. In the source database, you renamed it Order Details, without the underscore. You then compare the two databases. Before you propagate the schema from the source to the target, the data in both tables is identical. But when you write schema updates to the target, the Order_Details table is deleted, and a new Order Details table is created. You might lose all of the data in the Order_Details table.

You can open the Tools menu, click Options, and select the Block schema updates if data loss might occur check box to prevent data loss. In addition, you should always back up your database before you deploy changes to the database.

See Also

Tasks

How to: Compare the Schemas of Two Databases

Concepts

Overview of Comparing Database Data
Terminology Overview of Team Edition for Database Professionals