Overview of Comparing Database Data
In addition to comparing the schemas of databases, you can also compare the data between two databases that have the same schema. You can update the target database to match the data in some or all of the tables from the source database. You can also use this process to compare a source database with a newly deployed empty database and to fill the target database with the data from the source. In this process, you first connect to a database, compare the source data and target data, and then update the target data.
For example, a database administrator might oversee the life cycle for a team's database projects. Part of the database administrator's work is to prepare both a pre-release test database and a performance-testing database. The database administrator must synchronize the two databases at regular intervals over the course of the project. To perform this task, the database administrator establishes connections to both databases, sets the pre-release database as the source, and compares data by using Visual Studio Team System Database Edition. Finally, whenever necessary, the database administrator updates the performance database as the target.
For more information about how to compare data, see the following topics:
Connecting to a database: How to: Compare the Data of Two Databases.
Comparing source and target data:How to: Compare the Data of Two Databases.
Updating target data: How to: Synchronize Database Data.
This section describes various aspects of how you can compare data by using Database Edition.
Data Compare Requirements
Database Edition compares the data in tables and views. Each table or view that you want to compare in the source database must share several attributes with a table or view in the target database:
Tables and views must have the same name, the same owner, and matching schemas (or structure).
Names of tables, views, and owners are case-sensitive. For more information about database schemas, see How to: Compare the Schemas of Two Databases.
Tables must have the same primary key, unique index, or unique constraint.
Views must have the same unique, clustered index.
You can compare a table with a view only if they have the same name.
Note
Tables and views that do not meet these criteria are not compared and do not appear on the second page of the New Data Comparison wizard.
Database Edition uses a key or index to determine which objects correspond with each other. If a table or view in the source database has more than one primary key, unique index, or unique constraint that matches that of a table or view in the target database, you can specify which one to use for the comparison on the second page of the New Data Comparison wizard.
After you compare the data in the databases, you can view the results and then propagate data from the source to the target. For more information, see How to: View Data Differences.
Controlling Data Comparison
You can control the process of comparing data in the following ways:
You can configure the data comparison and the resulting update scripts through the Options dialog box. For more information, see How to: Set Options for Comparing Database Data.
You can specify which tables or views to compare. On the first page of the Data Compare wizard, you specify which databases to compare and you connect to them. You can also configure which records to include in the comparison results. On the second page, you can specify a subset of tables within the databases to compare. Only tables that have the same primary key, unique index, or unique constraint in both databases and views that have the same unique, clustered index in both databases appear in the list.
You can stop the comparison as long as it is in progress. If you stop the comparison, the data-compare window displays results for the tables and views that were compared up to that point. You cannot restart the comparison at the point where you stopped it, but you can refresh the comparison, which restarts it from the beginning.
For more information, see How to: Compare the Data of Two Databases.
Viewing Data Comparison Results
The Data Compare window occupies the main editing area of Visual Studio. The window displays comparison results for the tables and views in both databases. For each object that was compared, it displays a result and a proposed update action, as the following table describes:
Result Type |
Description |
Identical Records |
All of the records in the source object and the target object are identical. |
Different Records |
One or more records in the source and target objects differ. |
Only in Source |
One or more records exist in the source object but not in the target object. |
Only in Target |
One or more records exist in the target object but not in the source. |
You can display a subset of these results by filtering out unwanted categories. For example, you can display only records that differ between the databases or only records that exist in the target but not in the source. The Records View pane shows results for a particular database object by result type. For more information, see How to: View Data Differences.
After the comparison finishes, the Data Script Preview window displays the Data Manipulation Language (DML) script that you would run to update the records in the target database.
Refreshing Comparison Results
Before you start a comparison, you configure it by connecting to specific databases and, optionally, by also selecting specific objects within the databases to compare. You can compare the databases again using the same criteria by clicking Refresh. This action shows the current state of the databases, whether they have changed since the last time that you compared them.
If tables were added to the source database, the target database, or both since the last time that you compared them, they will not appear until you start a new data comparison. Refreshing the existing session will not pick up the changes.
Updating Data in the Target Database
After you compare data, you can update the data in the target database with the data in the source database. You can update all the objects in the database, selected objects only, or selected records in selected objects.
Selected check boxes in the Object column indicate the objects that would be updated in the target database if you clicked Write Updates. Clear the check box of an object if you do not want its records to be written to the target database.
For more information about how to specify which objects and records to update, see How to: Synchronize Database Data.