Quickly Compare and Synchronize SQL Server Database Contents with SQL Data Compare

The default mapping behavior is applicable for a new comparison project. Once the results are available, you may want to consider changes to these options. I would like to explain a few of these options:

  • Selecting Ignore case will map Northwind.dbo.Employees to NorthWind.dbo.emPloYeeS.
  • Selecting Ignore spaces will map Northwind.dbo.titleauthor to Northwind.dbo.title author.
  • Selecting Ignore underscores will map Northwind.dbo.pub_info to Northwind.dbo.pubinfo.

After setting all the configuration parameters, click Compare Now to complete the process of comparison.

Figure 9. Comparison completion notification.

Final Results

The completion report is shown below (figure 10). By default, all the rows with differences are included in the synchronization process. Rows that contain differences are displayed with a lightly shaded background; values that are different are displayed with a darker shaded background.

The yellow Interactive Help box displays information as you move the mouse pointer over the different areas of the main window.

Figure 10. Completion report.

The comparison results are grouped by object (figure 11):

Objects with differences in their rows.

Objects that contain only identical rows.

Objects that could not be compared.

Objects that were excluded from the comparison.

Figure 11. Detail from the completion report showing the object groups.

A few of the columns in the completion report are represented by icons, explained below.

Displays the number of rows for the object that exist in the source database on the left but do not exist in the target database on the right. If you chose to synchronize these rows by selecting the checkbox, they will be created in the target server’s database.

Displays the number of rows for the object that exist in both databases but are different. If selected for synchronization, the rows on the target database will be modified.

Displays the number of rows for the object that exist in the target database on the right but do not exist in the source database on the left. If chosen for synchronization, these rows will be deleted from the target database.

The Synchronization Wizard snippet’s color-coding is explained below.

Figure 12. Synchronization Wizard is red. Synchronization will occur from left to right (source to target).

If you choose to synchronize the databases, when the wizard is red (figure 12), it is set to synchronize from the source database on the left to the target database on the right, as indicated by the arrow. When the wizard is yellow (figure 13), it is set to synchronize from the target database on the right to the source database on the left. To change direction, double-click the arrow.

Figure 13. Synchronization Wizard is yellow. Synchronization will occur from right to left (target to source).

You can create a rollback script by changing the synchronization direction and regenerating the synchronization SQL script.

By default, a message will be displayed if there are no objects that require synchronization.

You can also view a side-by-side, color-coded listing of the differences in the creation SQL, by clicking an object.

Figure 14. Select the objects you want to synchronize.

To synchronize the databases, you first select the objects you want to synchronize (figure 14). The Synchronization Wizard enables the user to generate the synchronization SQL script and optionally synchronize the databases using the saved comparison project.

Figure 15. Step 1 of the Synchronization Wizard: Direction.

From the screenshot above, Step 1 of Synchronization Wizard (figure 15), it is evident that synchronization changes will be made to the “ProjectDBALooneyTunes” instance of the “Northwind” database. You can confirm the direction in which the changes will be applied by clicking Next.


Leave a comment

Your email address will not be published.