SQL Compare is a tool to Compare and synchronize SQL Server database structures. It is now used by more than 150,000 database administrators, developers, and testers. SQL Compare automates the process of allocating a database when testing a local database, staging or activating a database on a remote server.

This article is the second half of a working example of comparing and deploying two databases that has already been covered

Click to download the latest trial version of SQL Compare

3. Select the object to deploy

The comparison results are displayed in the Results pane:

The results were grouped as follows:

Objects exist in two databases but are different
An object that exists in WidgetStaging but does not exist in WidgetProduction
Identical objects existing in two databases


To view the objects in the group, click:

When an object is clicked, the (SQL Differences) pane below displays a side-by-side, color-coded list of the differences in the object creation script.

This example shows the WidgetPrices table:

For more information, see:

  • Viewing comparison Results

  • Viewing SQL differences

Use the check box in the middle of the above (Results) pane to select the object to deploy.

In this example, we will deploy all the different objects:

1. In the Filters pane, make sure all Object types in the default filter are selected. The current filter defines which objects to display. When a filter is used to exclude an object, it removes the object from the Results pane and cannot select the object for deployment.

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

3. Select all objects.

4. Click Deploy to open the Deployment dialog box.

Deploy the database

On the first page of the deployment wizard, you can choose to create and save a deployment script, or use SQL Compare to perform the deployment.

Selecting a deployment method

In this example, we will deploy using SQL Compare.

1. Ensure that Deploy with SQL Compare is selected.

2. If yes, clear the Pre-deployment Backup Target check box.

In this example, we will not take a backup before deployment.

3. Ensure that the Recomparison after Deployment check box is selected and the Save A copy of the deployment Script check box is selected.

In this example, we will run the script from SQL Compare and then Compare the database to check the results. We will also save a copy of the deployment script.

4. Click Next.

Review the script

The last page of the wizard displays information about deployment:

There are three tabs on the Review page:

  • Deployment script displays the script used to deploy the data source.

You can search for scripts, save scripts, or copy them to the clipboard.

  • The summary shows the action summary in the deployment script.

You can view a summary of the groups by affected object, modified type, or script modified target order.

  • Warnings Displays a list of any warnings about unexpected behavior that may occur while deploying the database.

In this example, SQL Compare displays a warning notifying you that it cannot change the IDENTITY column using the ALTER TABLE command, so the deployment script will rebuild the WidgetReferences TABLE.

A warning is displayed whenever a table needs to be rebuilt, because these operations can be slow. When a table is rebuilt, the data in the table is retained.

Perform the deployment

After viewing the script, deploy the database:

  • Click Deploy Now to deploy.

  • A confirmation dialog box will display. Click Deploy Now to continue.

  • SQL Compare displays a message dialog box showing the progress of the deployment. After the deployment is complete, click OK to close the message box.

SQL Compare then recompares the database. The results are displayed in the main window. In this example, all objects appear the same, confirming that the deployment was successful: