Compare and Synchronize Database Schemas

This topic applies to:

Visual Studio Ultimate	Visual Studio Premium	Visual Studio Professional	Visual Studio Express

You can use Visual Studio Premium or Visual Studio Ultimate 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 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

You can compare the following entities that contain database schema definitions:

• SQL Server database - a database that runs on a SQL Server 2008 or SQL Server 2005 instance

• Database project - a project that contains definitions for a SQL Server 2008 or SQL Server 2005 database and produces a .dbschema file when built

• Server project - a project that contains definitions for server objects and objects in the 'master' database on a SQL Server 2008 or SQL Server 2005 server and produces a .dbschema file when built

• Data-tier application component (DAC) project - a project that produces a .dacpac file when built

• .dbschema file - the output when you build a database or server project

• .dacpac file - the output when you build a data-tier application component (DAC) project

The following table shows the types of schemas that you can compare and whether you can write updates to the target:

Source down, Target across	SQL Server database	Database project	.dbschema file	Server project	DAC project	.dacpac file
SQL Server database	Compare + Update	Compare + Update	Compare	Compare + Update	Compare	Compare
Database project	Compare + Update	Compare + Update	Compare	None	None	None
.dbschema file	Compare + Update	Compare + Update	Compare	Compare + Update	None	None
Server project	Compare + Update	None	Compare	Compare + Update	None	None
DAC project	Compare	None	None	None	Compare + Update	Compare
.dacpac file	Compare	None	None	None	Compare + Update	Compare

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. This includes schemas in the form of a .dbschema file. You can, for example, compare a server project to a .dbschema file that was created from a server project.

You can compare two database projects only if they are contained within the same solution in Visual Studio.

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 extra 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. However, when you update the target, the Order_Details table is deleted, and an OrderDetails table is created. You might lose all 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. You can also specify options for a comparison when you compare schemas. For more information, see How to: Set Options for Comparing Database Schemas.

Important

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.

Differences Between Schema Compare and Incremental Deployment

When you deploy updates to an existing database, the process is very similar to the process when you use Schema Compare to synchronize two database schemas. However, Schema Compare has one important difference: by default, Schema Compare ignores extended properties.

You can change the options for Schema Compare, either for all Schema Compare sessions, or for the current session if you do not want to ignore extended properties and permissions. For more information, see How to: Set Options for Comparing Database Schemas.

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 and optionally update the target to match the source: 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. 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 Transact-SQL editor or to a file so that you can review it before applying the changes to the target.	How to: Compare Database Schemas Understanding Schema Compare Results
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 change a table definition in 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 dependencies.
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. Drop – 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 Drop, 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.