SQL Compare - 9.0

SQL Compare

Learning SQL Compare - 9.0

Worked example: Comparing and synchronizing two databases

This worked example demonstrates a basic comparison and synchronization of two SQL Server databases.

In the example, the Magic Widget Company has a SQL Server database running on a live web server. This database contains a number of tables, views, stored procedures, and other database objects. The Magic Widget Company's development team has been working on an upgrade to their website. As part of this upgrade, they have made a number of changes to the structure of the database. They have already transferred the changes from the development server to a staging server.

They now need to transfer the changes to the production server.

This example has four steps:

  1. Set up the databases

    Create the example databases on your SQL Server.

  2. Set up the comparison

    Specify the data sources you want to compare.

  3. Select objects to synchronize

    Review the results and select the objects you want to synchronize.

  4. Synchronize the databases

    Create and run a synchronization script.

1. Set up the databases

The worked example uses the following databases:

  • WidgetStaging is the staging database
  • WidgetProduction is the production database

To create these two databases on your SQL Server:

  1. If they already exist, delete the databases WidgetStaging and WidgetProduction.
  2. Click here to view the SQL creation script for the databases.
  3. Copy the script, paste it in your SQL editor application, and run it.

    The databases and their schema are created.

2. Set up the comparison

  1. Start SQL Compare if it is not already running. The Project Configuration dialog box is displayed showing your most recent project:

    You can edit the current project, or create a new project.

    If you want to create a new project, click Cancel to close the dialog box, and on the toolbar click (New Project)

  2. In the shaded upper pane, ensure both Source and Target are set to Database.

    In this example, we will compare databases. You can also compare backups, SQL Compare snapshots, and scripts folders.

  3. For each data source, in Server, type or select the name of the server on which you set up the databases.
  4. For the source, in Database, type or select WidgetStaging. Type or select WidgetProduction for the target.

    If the databases are not displayed in the Database lists, right-click in each Database box and click Refresh, or scroll to the top of the list and click Refresh.

  5. Click Compare Now.

    SQL Compare displays a message dialog box that shows the progress of the comparison.

    When the comparison is complete, click OK to close the message box.

3. Select objects to synchronize

The results of the comparison are displayed in the upper (Results) pane:

The results are grouped by:

objects that exist in both databases but are different

objects that exist in WidgetStaging but do not exist in WidgetProduction

objects that exist in both databases and are identical

To view the objects in a group, click :

When you click an object, the lower (SQL Differences) pane shows a side-by-side, color-coded listing of the differences in the object creation scripts.

This example shows the WidgetReferences table:

For more information, see:

Use the check boxes in the middle of the upper (Results) pane to select objects for synchronization.

In this example, we will synchronize all objects that are different:

  1. On the Filter pane, ensure the default filter Nothing Excluded is selected.

    The current filter defines which objects are displayed. When you use the filter to exclude an object, it is removed from the Results pane and cannot be selected for synchronization.

    For more information, see Using filters

  2. In the central column of the Results pane, select the top level check box to include all objects in the synchronization:

    All objects are selected, and the  Synchronization Wizard button becomes available.

  3. Click  Synchronization Wizard

4. Synchronize the databases

On the first page of the Synchronization Wizard you can choose to create and save a synchronization script, or perform the synchronization using SQL Compare.

Choose synchronization method

In this example, we will synchronize using SQL Compare.

  1. Ensure that Synchronize using SQL Compare is selected.
  2. Clear the Back up target before synchronization check box if it is selected.

    In this example, we will not back up before synchronization.

    For more information, see Backing up before synchronization

  3. Ensure the Recompare after synchronization check box, and the Save a copy of the synchronization script check box are selected.

    In this example, we will run the script from SQL Compare, and compare the databases afterwards to check the results. We will also save a copy of the synchronization script.

  4. Click Next

Review dependencies

SQL Compare checks for dependencies:

All the objects you selected for synchronization are listed. If the selected objects reference or are referenced by objects you did not select, and these dependencies affect the synchronization, the objects with dependencies are listed here.

For more information, see Using the synchronization wizard

In this example there are no dependencies. Click Next to review the synchronization script.

Review script

The final page of the wizard displays information about the synchronization:

There are three tabs on the Review page:

  • Synchronization script shows the script to synchronize the data sources.

    You can search the script, save it, or copy it to the clipboard.

  • Summary shows a synopsis of the actions in the synchronization script.

    You can view the summary grouped by the objects affected, by the type of modification, or by the order in which the script modifies the target.

  • Warnings shows a list of any warnings about unexpected behavior that may occur when you synchronize the databases.

    For more information, see Warnings

In this example, SQL Compare displays a warning to inform you that it cannot use the ALTER TABLE command to change the IDENTITY column, so the synchronization script will rebuild the WidgetReferences table.

Warnings are displayed whenever tables require rebuilding as these may be slow operations. Data in tables is preserved when tables are rebuilt.

Performing the synchronization

When you have reviewed the script, synchronize the databases:

  1. Click Synchronize Now to perform the synchronization.
  2. A confirmation dialog box is displayed. Click Synchronize Now to continue.
  3. SQL Compare displays a message dialog box that shows the progress of the synchronization.

    When the synchronization is complete, click OK to close the message box.

SQL Compare then re-compares the databases. The results are shown in the main window. In this example, all objects are shown to be identical, confirming that the synchronization has been a success:

See also

Understanding the comparison results

Understanding the synchronization

Working with projects

Setting data sources

Setting project options

Viewing the comparison results

Setting up the synchronization

Using the Synchronization wizard

Was this article helpful?

Search support
Forums
Visit the SQL Compare forum.
Learning SQL Compare
Knowledge base

SQL Compare

all SQL products

all products

Notes & articles

SQL Compare

all SQL products

all products

Demos & walkthroughs

SQL Compare