How to: Compare the Data of Two Databases

  • Article

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.

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 Overview of Comparing Database Data.

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 the Schemas of Two Databases.

Compare Database Data

To compare the data of two databases

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

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

    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.

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

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

  5. 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 the Schemas of Two Databases
How to: View Data Differences

Concepts

Overview of Comparing Database Data
Terminology Overview of Team Edition for Database Professionals