How to: Compare the Data of Two Databases
By using Visual Studio Team System Database Edition, you can compare the data that is contained in two databases. The databases that you compare are known as the source and the target.
Note
Database projects contain no data. Therefore, a database project cannot be the source or the target in a data comparison.
As the data is compared, a Data Manipulation Language (DML) script is generated that you can use to synchronize the differing databases by updating some or all of the data on the target database. When the data comparison finishes, its results appear in the Data Compare window of Visual Studio. For more information, see Compare and Synchronize Data in One or More Tables with Data in a Reference Database.
Note
You can also compare the schema of two databases or of two versions of the same database. For more information, see How to: Compare Database Schemas.
Compare Database Data
To compare the data of two databases
On the Data menu, point to Data Compare and then click New Data Comparison.
The New Data Comparison wizard appears. Also, the Data Compare window opens, and Visual Studio automatically assigns it a name such as DataCompare1.
Identify the source and target databases.
If the Source Database list or Target Database list is empty, click New Connection. For more information about how to create, rename, modify, and delete connections, see How to: Create a Database Connection. On the Connection Properties dialog box, identify the server on which the database resides and the type of authentication to use when connecting to the database. Then, click OK to close the Connection Properties dialog box and return to the Data Compare wizard.
On the first page of the Data Comparewizard, verify that the information for each database is correct, specify which records you want to include in the results, and then click Next. The second page of the Data Comparewizard appears and shows a hierarchical listing of the tables and views in the database.
Note
Tables and views must meet two criteria to appear in appear in the listing. First, the schemas of the objects must match between the source and target database. Second, only tables and views that have a primary key or a unique key appear in the list. If no tables or views meet both criteria, the list will be empty.
Select the check boxes for the tables and views that you want to compare. Optionally, expand the nodes for database objects, and then select the check boxes for columns within those objects that you want to compare.
Note
Each table or view that you want to compare must have a matching primary key, a matching index, or a unique key defined. Otherwise, the table is removed from the list of tables that will be compared.
For some objects, you can use the Comparison Key column to specify which key on which to base the data comparison. For example, you can specify whether to base the comparison on the primary key column or on another (uniquely identifiable) key column.
Click Finish.
The comparison starts.
Note
You can stop a data comparison operation that is in progress by opening the Data menu, clicking Data Compare, and then clicking Stop Data Comparison.
When the comparison is finished, you can view the data differences between the two databases. You can also update part or all the data in the target database to match the data in the source database. For more information, see How to: View Data Differences and How to: Synchronize Database Data.
See Also
Tasks
How to: Synchronize Database Data
How to: Compare Database Schemas
Concepts
Compare and Synchronize Data in One or More Tables with Data in a Reference Database