How to: Compare the Data of Two Databases
By using Team Edition for Database Professionals, you can compare the data that is contained in two databases. The databases that you compare are known as the source and the target.
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.
You can also compare the schema of two databases or of two versions of the same database. For more information, see.
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 Data Compare wizard appears. Also, the Data Compare window opens, and Visual Studio automatically assigns it a name such as DataCompare1.
In the Data Compare wizard, identify the source and target databases.
If the Source Database list or Target Database list is empty, click New 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 Compare wizard, 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 Compare wizard appears and shows a hierarchical listing of the tables and views in the database.
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.
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.
The comparison starts.
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, seeand .