Compare and Synchronize Database Schemas

You can use Visual Studio Team System Database Edition to compare a target schema with a source schema. For each difference, you can specify whether to update the target so that it matches the source. You can write those updates directly to the target, or you can export the update script to the Transact-SQL (T-SQL) editor or to a file. You can also save your comparisons, either as part of your database project or as a stand-alone file. By saving your comparisons, you can more easily repeat them or re-synchronize the same source and target.

Types of Schema Comparisons

The following table shows the types of schemas that you can compare. The first column lists the source schemas, and the first row shows the target schemas.

Source down, Target across	Database or Server	Project (.dbproj)	Project file (.dbschema)
Database or Server	Compare and Update	Compare and Update This type of comparison is not recommended. See the note after this table.	Compare only
Project (.dbproj)	Compare and Update	Compare and Update	Compare only
Profile file (.dbschema)	Compare and Update	Compare and Update	Compare only

To read this table, find the row for your source schema in the left-most column. Next, find the column for your target schema in the top row. The intersection of that column and row provides information about whether you can only compare the source and target, or whether you compare and then optionally update the target schema.

You cannot compare a server schema to a database schema or a database schema to a server schema.

Warning

Although you can compare a source database with a target database project (.dbproj), you should instead change the database project and deploy those changes to the database. When you change the project (.dbproj) and then deploy it, you reduce the risk of your changes conflicting with changes that another member of the team made because the project is under version control.

Preventing Data Loss when Updating Database Schemas

When you use Schema Compare to update database schemas, you can cause data loss. To avoid data loss, 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, you might rename a table from Order_Details to OrderDetails in the source database but not the target database and then compare the two databases. Before you synchronize them, the data in both tables is identical. But when you update the target, the Order_Details table is deleted, and an OrderDetails table is created. You might lose all of the data in the Order_Details table.

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

Important Note:
If you use refactoring to rename objects in your database project, the refactoring log also helps prevent data loss when you build and deploy the database. This log preserves the intent of your changes. For example, tables can be renamed in place.

Common Tasks

In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks.

Common Tasks	Supporting Content
Get hands-on practice: You can follow introductory walkthroughs to become familiar with how to compare two databases or a database and a project (.dbproj).	Walkthrough: Comparing the Schemas of Two Databases Walkthrough: Comparing the Schemas of a Database and Database Project
Set options to control how the schemas are compared: You can configure details that control how the schemas are compared. You can ignore specific types of differences, such as whitespace, file groups, and comments. You can also ignore categories of objects, such as users or logins. You can specify options that control the generated update script.	How to: Set Options for Comparing Database Schemas
Compare database or server objects: You specify a source and target schema to compare, and the results appear in the Schema Compare window. You can view details of the differences and the update script that will be used to synchronize the database. You can save each schema comparison to your project or to a stand-alone .scmp file.	How to: Compare Database Schemas Understanding Schema Compare Results How to: View Schema Differences
Update the target to match the source: You specify actions for each difference between the source and the target schemas, and then you can write updates to the target. You can also export the update script to the T-SQL editor or to a file so that you can review it before applying the changes to the target.	How to: Synchronize Database or Server Objects
Troubleshoot problems: You can learn more about how to troubleshoot common problems that you might encounter when you compare and synchronize database schemas.	Troubleshooting Schema Compare Issues

Understanding Schema Compare Results

The Schema Compare window displays four columns for each object that is compared. The following table describes the contents of each column:

Column	Values
Status	Equal - The object has the same definition and contains the same objects in the source schema and the target schema. New - The object exists in the source schema but not in the target schema. Missing - The object exists in the target schema but not in the source schema. Different Definition - The object has a different definition in the source schema than it does in the target schema. Different Dependencies - The object has the same definition but contains different objects in the source schema and the target schema. For example, you could add a table to the source schema but not the target schema and then compare them. The tables will be marked as Different Definition, and the schema that contains those tables will be marked as Different Objects.
SourceName (Source {Project, Database, or Project File (.dbschema)})	The name of the source project, database, or project file that you are comparing.
Update Action	Skip - The object will not be updated in the target schema. If the object has dependencies that have changed, the icon will indicate that you should expand this row to see the referenced object that is different. Skip Referenced - The object exists in a referenced database and does not need to be dropped or created. Create - The object will be created when you write updates or when you run the update script. Update - The object definition will be updated to match the source schema when you write updates or when you run the update script. Delete - The object will be removed from the target schema when you write updates or when you run the update script. Note: The default action is determined by the status. For Equal objects, the default action is Skip, and you cannot change it. For New objects, the default action is Create, but you can specify Skip. For Missing objects, the default action is Delete, but you can specify Skip. For Different Definition, the default action is Update, but you can specify Skip. For Different Objects, the default action is Skip, and you cannot change it. (In this case, the object is equal, but it contains objects that are new, missing, or changed.)
TargetName (Target {Project, Database, or Project File (.dbschema)})	The name of the target project, database, or project file that you are comparing.

Related Scenarios

• Compare and Synchronize Data in One or More Tables with Data in a Reference Database
  You can use Data Compare to synchronize data between tables in source and target databases.

• Rename All References to a Database Object
  You can use refactoring to rename all references to a database object. When you build and deploy, the refactoring log is used to preserve the intent of your changes and reduce the risk of data loss.

• Build and Deploy Databases to an Isolated Development Environment
  You can update the target database by using Schema Compare, but teams should consider changing the database project and then building and deploying those changes to their target database. If you follow this practice, you preserve the intent of any refactoring operations that you perform on the database project. You also reduce the risk of having your changes overwritten by another team member who also uses Schema Compare to update your database.

See Also

Concepts

Terminology Overview of Database Edition

Change History

Date	History	Reason
June 2010	Added information about how to read the first table in the topic to address customer feedback.	Customer feedback.