How to: Compare Database Schemas
By using Visual Studio Team System Database Edition, you can compare two database schemas. Those schemas might be represented by a database, 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, Database Edition 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 regenerated 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 How to: View Schema Differences.
You can update part or all of the target to match the source. For more information, see How to: Synchronize Database or Server Objects.
You can compare the data that resides in the target and the source. For more information, see How to: Compare the Data of Two Databases.
For more information about schema-comparison scenarios, see Compare and Synchronize Database Schemas.
To compare two schemas
If you want to compare two schemas but not save your settings as part of your database project, perform the following steps:
Open the Data menu, point to Schema Compare, and click New Schema Comparison.
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:
In Solution Explorer, right-click the Schema Comparisons folder, point to Add, and click Schema Comparison.
The Add New Item dialog box appears.
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.
If you clicked Database for the source or the target, connect to the data source by clicking it in the list.
If no databases are 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.
(optional) Click SQLCMD Variables to specify a .sqlcmdvars file that contains a list of the variables and their values.
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 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.
See Also
Tasks
How to: Synchronize Database or Server Objects
How to: View Schema Differences
How to: Compare the Data of Two Databases