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

If you choose to compare a single table object between the servers, select the object. Then, if your selection is limited to a number of rows with a WHERE clause, click the filter button to show the WHERE Clause Editor (figure 5) for the selected row.

Figure 5. WHERE Clause Editor.

In the WHERE Clause Editor, if you want to use a different WHERE clause for each object, clear the Use the same WHERE clause for both objects check box and type the other WHERE clause in the box on the right.

The mappings on the Remap Objects tab (figure 6) are useful for identifying the objects that are used to compare within two databases.

Figure 6. Remap Objects tab.

The mappings work on objects with the same name and same owner in SQL Server 2000. An additional feature is available in SQL Server 2005 is schema-based mapping.

Figure 7. Detail from the Remap Objects tab.

In the screenshot above (figure 7), the upper pane displays a list of schemas, database roles, and database users that SQL Data Compare has automatically mapped for you. The lower pane displays a list of schemas, database roles, and database users that could not be mapped. SQL Data Compare cannot compare certain combinations of data types. For example, a timestamp column can be compared, but the tool cannot synchronize the timestamp columns.

Options (figure 8), the last tab in Project Configuration, is an advanced feature that enables you to modify the default behavior of SQL Data Compare. You can set options to disable and re-enable triggers between two comparison projects to synchronize the databases.

Figure 8. Options tab.

Restore Defaults will apply default settings such as Ignore case, Ignore space, Include indexed views, Include identity columns, Include timestamp columns, Disable foreign keys, Use transactions in SQL Scripts, and Show identical values in results. If you move the mouse over each mapping behavior, you'll see a description of that behavior.