How to: Compare Database Schemas

Article
02/04/2013

This topic applies to:

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

By using Visual Studio Premium or Visual Studio Ultimate, you can compare two database schemas. Those schemas might be represented by a database, a data-tier application project, a database project, or the build output from a database project (a .dbschema file). The entities that you compare are known as the source and the target. When the schema comparison finishes, its results appear in the Schema Compare window. Also, Visual Studio generates a Data Definition Language (DDL) script that you can use to synchronize the different schemas.

If you plan to compare a database project to a target database on a recurring basis, you can add the schema comparison to your database project. You can then re-compare the schemas by re-opening that comparison. By saving a schema comparison, you save connection information, session-specific options, and values for SQLCMD variables. Results are not saved, and they will be re-generated when you open the .scmp file for the saved schema comparison.

After the comparison finishes, you can take other steps:

You can view the structural differences between the two databases. For more information, see Viewing Schema Differences.
You can click Refresh Update Script to see the full script that will be used to synchronize the schemas if you decide to do so.
You can update part or the entire target to match the source. For more information, see Synchronizing Database or Server Objects.
You can compare the data that resides in the target and the source. For more information, see How to: Compare and Synchronize the Data of Two Databases.

For more information about schema-comparison scenarios, see Compare and Synchronize Database Schemas.

You can also compare .dbschema files at a command prompt by using VSDBCMD.EXE. You might do this, for example, to generate an update script to update a target database when you do not have direct access to the target server.

In this topic

To compare two schemas by using Schema Compare

If you want to compare two schemas but not save your settings as part of your database project, perform the following steps:
1. Open the Data menu, point to Schema Compare, and click New Schema Comparison.
2. Skip to step 3.
If you want to compare two schemas and then save your settings as part of your database project, perform the following steps:
1. In Solution Explorer, right-click the Schema Comparisons folder, point to Add, and click Schema Comparison.
  
  The Add New Item dialog box appears.
2. In Name, type the name that you want to give the schema comparison, and then click Add.
Specify the source and the target that you want to compare by clicking Project, Database, or Database schema file for each schema.

Note

You can specify a database schema file for the target schema, but you cannot update it. For a full list of the supported comparison scenarios, see Compare and Synchronize Database Schemas.
If you clicked Database for the source or the target, connect to the data source by clicking it in the list.

If your database is not listed, click New Connection. In the Connection Properties dialog box, 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.
If you clicked Database schema file for the source or the target, type the path and file name, or click Browse to specify a file.
(optional) Click Options to specify which objects are compared, what types of differences are ignored, and what the generated update script will contain.

Important

By default, Schema Compare sessions use the options that you configure when you open the Tools menu and click Options. If you do not want to ignore extended properties, click Options to change the corresponding setting. For more information about setting options for Schema Compare, see How to: Set Options for Comparing Database Schemas.
(optional) Click SQLCMD Variables to specify a .sqlcmdvars file that contains a list of the variables and their values.

Note

You can only specify SQLCMD variables when comparing two projects (database projects, server projects, or Data-tier Application projects).

The values will be substituted in the corresponding database project when the schemas are compared.
Click OK.

The schema comparison starts.

Note

You can stop a comparison that is in progress by clicking Stop on the toolbar.

To compare schemas by using the Visual Studio automation model

Open the View menu, point to Other Windows, and click Command Window.
In the Command Window, type the following command:
```
Data.NewSchemaComparison sourceIdentifier targetIdentifier
```
Replace sourceIdentifier with one of the following:
- /ProviderType ConnectionBased /ConnectionString "myConnectString"
- /ProviderType ConnectionBased /ConnectionName myConnectionName
- /ProviderType ProjectBased /ProjectName myFileName.dbproj
- /ProviderType FileBased /ProjectName myFileName.dbschema
- /ProviderType FileBased /ProjectName myFileName.dacpac
Replace targetIdentifier with one of the following:
- /ProviderType ConnectionBased /ConnectionString "myConnectString"
- /ProviderType ConnectionBased /ConnectionName myConnectionName
- /ProviderType ProjectBased /ProjectName myFileName.dbproj
- /ProviderType FileBased /ProjectName myFileName.dbschema
- /ProviderType FileBased /ProjectName myFileName.dacpac
If you do not specify a source and a target, the New Schema Comparison dialog box appears. For more information about the parameters for the Data.NewSchemaComparison command, see Automation Command Reference for Database Features of Visual Studio.

The objects and settings in the specified source and target are compared. The results appear in a Schema Compare session that you can optionally save. For more information about how to view results or synchronize the schemas, see Viewing Schema Differences and Synchronizing Database or Server Objects.

To save a schema comparison for the first time

On the File menu, click Save SchemaComparisonName.scmp.

The Save File As dialog box opens.
Specify the path and the file name for the schema comparison.

If the comparison is part of your database project, the comparison will be saved in the SchemaComparisons folder of that project.
Click Save.

Your schema comparison is saved to the location that you specified.

Viewing Schema Differences

You can determine how a source schema differs from a target schema by comparing them and viewing the results as a tree in the Schema Compare window. For example, you can show only those objects that exist in the source but not in the target. You can also view details about how particular objects differ, and you can refresh the results to reflect recent changes.

You can also view the results as Transact-SQL commands in a script that would synchronize the target with the source. You can display this script in the Schema Update Script window or the Transact-SQL editor, and you can also export the script to a file. If you decide to update the target to match the source, you can immediately run the script to synchronize the schemas, or you can modify the script and then run it. For more information, see Synchronizing Database or Server Objects.

To filter the results by type

On the Schema Compare toolbar, click Filter.
Click one or more filters to specify which sets of results you want to display.

Note

Filter settings are saved with the .scmp file and persist when you change source and target schemas.

To view changes within an object definition

Click any row in the grid where the status is New, Missing, or Different by Shape.

To refresh comparison results

On the Schema Compare toolbar, click Refresh.

The source and target are compared again, and the results of the comparison are updated.

To view the synchronization script

On the Data menu, point to Schema Compare, and click Show Schema Update Script.

Note

If Show Schema Update Script is unavailable, either scripts cannot be generated for the target that you specified or that the comparison needs to be refreshed. For example, if your target schema is a .dbschema file, you cannot create an update script.

The Schema Update Script window appears and shows the script that you can use to update the target to match the source.

Note

If the Schema Update Script window is open, you can also click Refresh Schema Update Scripts.

To open the synchronization script in the Transact-SQL editor

On the Data menu, point to Schema Compare, point to Export to, and click Editor.

The Transact-SQL editor appears and shows the script that you can use to update the target to match the source.

To save the synchronization script to a file

On the Data menu, point to Schema Compare, point to Export to, and click File.

The Save Update Schema Script dialog box appears.
In Object name, type the name that you want to give the synchronization script, and click Save.

The script is saved to the file name that you specified.

Synchronizing Database or Server Objects

After you compare the schemas of the source and the target, you can synchronize an entire schema or only the database objects that you specify within that schema. For more information, see Compare and Synchronize Database Schemas.

If both source and target are on the same server, an error might occur when you try to synchronize the schemas. The error occurs because the target files, such as filegroup files, cannot be created with the same name as the source as they already exist on the server. To resolve this issue, change the Update Action to Skip for the files that conflict.

Note

The databases that you compare are known as the source and the target. When you synchronize database schemas, you update the target and leave the source unchanged. Although you can specify a project file (.dbschema) as the target, you cannot update a project file.

To update the target schema

Compare two schemas.

After the comparison finishes, the Schema Compare window lists the database objects that were compared. Each row represents one database object.
(Optional) In the Update Action column, specify whether to apply or skip the update to each object in the results list.

Individual synchronization actions are not performed immediately but stored for batch execution in the following step.

Note

To reset the Update Action column for all objects of a particular type, right-click the node for that type (for example, the Tables node), and click Restore Defaults. To set the Update Action column for all objects of a particular type to a specific state, right-click the node for that type, and click Create All, Update All, or Skip All.
To synchronize database objects that are different, missing, or new, do one of the following:
- To update the target immediately, click Write Updates.
  
  Except for objects marked as Skip, this choice applies the schema of the selected database objects in the source on the corresponding objects in the target. This synchronization means updating, creating, or dropping the target object.
  
  Important
  
  While the schema is being updated, you can cancel the operation by clicking Stop Writing to Target. If you stop the update, no changes are propagated for most object types. However, partial changes to User and Role objects might not get rolled back because these objects cannot be wrapped in transactions.
- To review changes before updating the target, use the Schema Update Script window or click Export to Editor.
  
  This choice generates a Transact-SQL script and opens it in a Transact-SQL editor window. You can review and, if necessary, modify the script before you run it against the target. The generated Transact-SQL script matches the script that would be run if you clicked Write Updates.
  
  Note
  
  Visual Studio does not automatically refresh the results that appear in the Schema Compare window after the Write Updates action finishes. Instead, the status bar is updated to suggest that you click Refresh. This behavior gives you the choice to compare schemas again, an action that could take a long time if the schemas are large.
- To save changes to a file without viewing them in the Transact-SQL editor, click Export to File.
  
  This choice generates a Transact-SQL script and saves it to the file name that you specify. The generated script matches the script that would be run if you clicked Write Updates. You might take this approach if another person will update the target later with the script that you provide or if additional changes must be made to the script by another person.

Compare Schemas by using VSDBCMD.EXE

You might want to generate a deployment script for a database to which you do not have access. You can accomplish this task by using VSDBCMD.EXE if you have the .dbschema file for the project that you want to deploy and the .dbschema that represents the current state of the target database. If you do not have the target schema, you can ask your database administrator to import it for you by using the second procedure in this section.

You can perform both these procedures on any computer that has the VSDBCMD.EXE tool and the supporting files. You can find all the required files in the following folder and its subfolders: %PROGRAM FILES%\Microsoft Visual Studio 10.0\VSTSDB\Deploy. For more information, see Command-Line Reference for VSDBCMD.EXE (Deployment and Schema Import).

To compare schemas at a command prompt

Open a command prompt.
Change folders to the folder that contains VSDBCMD.EXE.
At the command prompt, type the following command: vsdbcmd.exe /a:deploy /dd:- /dsp:sql /model:ProjectName.dbschema /targetmodelfile:TargetDatabase.dbschema /DeploymentScriptFile:OutputFilename.sql /p:TargetDatabase="TargetDatabaseName"

Replace ProjectName with the name of the .dbschema file that you created by building your database project. Replace TargetDatabase with the name of the .dbschema file that contains the schema for the target database. Replace OutputFilename with the name that you want to give the deployment script. Replace TargetDatabaseName with the name of the database that you want to update.

Note

If you want to include the pre-deployment and post-deployment script from the database project, you can add one parameter to the command line: /ManifestFile:ProjectName.deploymanifest. You can find the .deploymanifest file in a subfolder of the sql folder under your project folder.
You can now use the OutputFilename.sql file to deploy updates to the target server, or you can ask your database administrator to do so.

To import the target schema

Open a command prompt.
Browse to the folder that contains VSDBCMD.EXE.
At the command prompt, type the following command: vsdbcmd.exe /a:Import /dsp:sql /model:TargetDatabase.dbschema /cs:ConnectionString

Replace TargetDatabase with the name of the .dbschema file that you are importing from the target database. Replace ConnectionString with a connection string to the target database. For example, you might specify /cs:"Data Source=MyServer/SQL2K8;Integrated Security=True;Pooling=False;Initial Catalog=MyDatabase".

When the command is completed, the .dbschema file is created. It contains the schema definition for the database from which you imported it. For more information, see How to: Import a Schema from a Command Prompt and Command-Line Reference for VSDBCMD.EXE (Deployment and Schema Import).