How to: Synchronize Database Data
After you compare the data in two databases, you can synchronize them by updating all or part of the target to match the source. You can compare the data in two kinds of database objects: tables and views.
Synchronize Database Data
To update target data by using the Write Updates command
Compare the data in two databases. For more information, see How to: Compare the Data of Two Databases.
After the comparison finishes, the Data Compare window lists results for the objects that were compared. Four columns, which are named Different Records, Only in Source, and Only in Target, and Identical Records, display information for objects that were not identical. For each such object, these columns display how many records were found to be different, and how many records an update operation would change. Those two numbers match at first, but in step 4 you can change which objects to update.
For more information, see Compare and Synchronize Data in One or More Tables with Data in a Reference Database.
In the table of the Data Compare window, click a row.
The details pane shows results for the records in the database object that you clicked. Records are grouped by status onto tabs, which you can use to specify the data that will be propagated from the source to the target.
In the details pane, click a tab whose name contains a number other than zero (0).
The Update column of the Only in Target table contains check boxes that you can use to select rows. By default, each check box is selected by default.
Clear check boxes for records in the target that you do not want to update with data from the source.
When you clear a check box, you reduce the number of records to update, and the display changes to reflect your actions. This number appears in the status line of the details pane and in the corresponding column in the main results pane, as described under step 1.
(Optional) Click Export to Editor.
A Transact-SQL (T-SQL) editor window opens and shows the Data Manipulation Language (DML) script that would be used to update the target.
To synchronize records that are different, missing, or new, click Write Updates.
Note
While the target database is being updated, you can cancel the operation by clicking Stop Writing to Target.
The data of the selected records in the target is updated with the data from the corresponding records in the source.
Note
If you opt to update indexed views, the Write Updates operation might fail if this action causes duplicate keys to be inserted into the same table.
To update target data by using a T-SQL script
Compare the data in two databases. For more information, see How to: Compare the Data of Two Databases.
After the comparison finishes, the Data Compare window lists the objects that were compared. For more information, see Compare and Synchronize Data in One or More Tables with Data in a Reference Database.
(Optional) In the details pane, clear the check boxes for records in the target that you do not want to update, as described in the previous procedure.
Click Export to Editor.
A new window shows the T-SQL script that would propagate the changes necessary to make the data in the target match the data in the source. The new window is given a name such as Server.Database - DataUpdate_Database _1.sql.
This script reflects changes that you have made in the details pane. For example, you might have cleared a check box for a given row in the Only in Target page for the [dbo].[Shippers] table. In that case, the script would not update that row.
(Optional) Edit this script in the Server.Database - DataUpdate_Database _1.sql window.
(Optional but recommended) Back up the target database.
Click Execute SQL or press F5 to update the target database.
.gif "Important note")
Important Note:By default, the updates occur within the scope of a transaction. If errors occur, you can roll back the whole update. You can change this behavior. For more information, see How to: Set Options for Comparing Database Data.
The data of the selected records in the target is updated with the data from the corresponding records in the source.
See Also
Tasks
How to: Compare the Data of Two Databases
Walkthrough: Comparing the Data of Two Databases