This worked example demonstrates a basic comparison and deployment 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've made a number of changes to the database, which need to be transferred to the production database.
You can follow the example on your own system. You'll need access to a SQL Server to do this.
This example has four steps:
The worked example uses the following sample databases:
To create the databases on your SQL Server:
Copy the script, paste it in your SQL editor application, and run it.
If databases named WidgetDev, WidgetTest or WidgetLive already exist on your SQL Server, the script will overwrite them. |
The databases are created and populated with data.
In this example, there are three steps to setting up the comparison:
It's not always necessary to set object mappings. However, in this example there are some schema differences between the source and target. For more information, see Mapping objects |
(New Project). 
| By default, SQL Data Compare does not compare views. |
To include views, on the Options tab, select the check box for Include views:
The Options tab allows you to modify the behavior of SQL Data Compare during comparison and deployment.
| For the WidgetPrices table, only 4 out of the 6 columns will be compared, as two of the column names do not match. To include the remaining columns, you must map the objects |
Map to map the columns.The columns move to the upper pane of the dialog box.SQL Data Compare displays a message dialog box that shows the progress of the comparison. If you select the Close dialog box on completion check box, SQL Data Compare closes this message dialog box automatically the next time that you run a comparison on a project. For this example, leave the setting as it is, and click OK to close the message box.
The comparison results are displayed in the main window.
A yellow warning bar is displayed indicating that SQL Data Compare has automatically mapped columns in the WidgetPrices table:
In this example we mapped the columns manually, so we can ignore this warning. |
In this example, the comparison results are grouped by:
tables or views with differences in their rows
tables or views with identical rows only
To display the comparison results in a single list, in the Group by box, select No groups.
To display the comparison results in groups, select Type of difference.
To view the comparison results for each object group, click 
or click the grouping bar:
The upper (Results) pane also shows how many rows of each type exist for each table or view. For example, the table WidgetPrices contains:
The Identical column shows that there are two rows that are identical.
To locate objects, type the search text in the Find box. To select a recent search, click the Find arrow button 
As you type, objects are grouped in the upper pane by whether they match or do not match what you type:
SQL Data Compare searches object names and owners (schemas).
Note that the search is not case-sensitive.
To clear the Find box click the 
button.
To view detailed information about the data in a table or view, click on it in the upper pane. The lower (Row Differences) pane is displayed.
Click the table WidgetDescriptions to see the row differences:
In this example WidgetID is the comparison key 
, used by SQL Data Compare to match rows in the two databases.
For the row where WidgetId is 2, the values for Short Description and Description are the same in both databases, so the data is displayed in gray text.
The Picture values are different so they are displayed with the darker shaded background.
To view a specific value in a row double-click it, or right-click and select Show Viewer. Binary Data values are displayed as links. Click the Binary Data link for the row where WidgetID is 2:
For tables or views that contain a large number of columns, you may find it useful to display the column values vertically so that you can see records more easily. To do this, click Show, then Pivot View:
To display the column values horizontally again, click Show, then Pivot View.
You can move through each difference in turn using the 
and 
buttons. To move through the differences for the current column, click 
or 
Use the check boxes in each pane to select data for deployment. You can select individual tables or views to deploy, and for each table or view you can select the rows that you want to deploy. By default, all rows that differ are selected when you run a comparison.
For this example, select all the tables and their rows. Make sure that the check boxes are cleared for the WidgetPriceList view. You do this by clearing the All Different check box for WidgetPriceList:
The number of rows selected for deployment is shown in the Direction Bar.
To deploy, click Deployment Wizard.
On the first page of the deployment Wizard you can choose to create and save a deployment script, or perform the deployment using SQL Data Compare.
In this example, we will deploy using SQL Data Compare.
There are three tabs on the Review page:
For more information, see Deployment warnings.
When you have reviewed the script, deploy the databases:
SQL Data Compare then re-compares the databases. The results are shown in the main window. In this example, all tables and their rows are shown to be identical, confirming that the deployment has been a success.
Note that the WidgetPriceList views are also now identical, because the tables that they reference have been deployed:
