Export (0) Print
Expand All

Compare and Synchronize Database Schemas

This topic applies to:

Visual Studio Ultimate

Visual Studio Premium

Visual Studio Professional 

Visual Studio Express

Topic appliesTopic appliesTopic does not applyTopic does not apply

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.

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.

Caution noteCaution

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.

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 noteImportant

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.

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.

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

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.

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

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.

Community Additions

ADD
Show:
© 2014 Microsoft