Getting started

SQL Compare enables you to compare and synchronize the structure of two Microsoft® SQL Server™ databases.

This is useful, for example, in a development environment when changes made to a local database need to be transferred to a live database on a remote server. Traditionally, this meant spending hours scrutinizing the database structure and hand-generating migration scripts. SQL Compare automates this process for you.

You can use SQL Compare to compare and synchronize SQL Server 2008, SQL Server 2005 and SQL Server 2000 databases.

SQL Compare: step-by-step

  1. Choose the data sources to compare
  2. View the comparison results
  3. Set up the synchronization
  4. Create the synchronization script

Worked examples

Learn more about SQL Compare by following these detailed examples:

  • Comparing and synchronizing two databases
  • Using SQL scripts as a data source

Note that you can follow the second worked example only if you are using SQL Compare Professional edition.

Related products

SQL Changeset is a free tool that can be used with SQL Compare Professional Edition for integration with your source control system.

For more information, see: Getting started with SQL Changeset

Getting more information

  • What's new in this version of SQL Compare?
  • Find out about activating SQL Compare.
  • How can I check for updates to SQL Compare?
  • Find out more about Red Gate.

© Red Gate Software Ltd. All Rights Reserved.


Using SQL Compare

In this chapter

  • Worked example: Comparing and synchronizing two databases
  • Worked example: Using a scripts folder as a data source
  • Comparing databases on unconnected SQL Servers
  • Comparing databases on different SQL Server versions
  • Creating a rollback script


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 [image] (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 [image] :

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:

  • Viewing the comparison results
  • Viewing the SQL differences

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 [image] Synchronization Wizard button become available.

  3. Click [image] 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

Worked example: Using a scripts folder as a data source

Comparing databases on unconnected SQL Servers

Comparing databases on different SQL Server versions

Creating a rollback script

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


Worked example: Using a scripts folder as a data source

This worked example demonstrates the use of a scripts folder when comparing and synchronizing databases. A scripts folder contains SQL script files representing a database's structure and, optionally, data.

For more information, see Working with scripts folders

In the example, the Super Sprocket Company has a SQL Server database that contains a number of tables, views, stored procedures, and other database objects. The Super Sprocket Company's development team has been given the task of making a number of changes to the structure of the database and updating the production server.

A copy of the production database has already been restored to an empty database, ready for development. To ensure no untested changes are made to the production database, they will also save its schema as a scripts folder.

You can follow the example on your own system, if you are using SQL Compare Professional edition. You will need access to a SQL Server to do this.

If you have not already followed the Comparing and synchronizing two databases worked example, you are recommended to do so before starting this worked example.

This example has four stages:

  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. Create a synchronization script

    Create a script to update the production database.

Note that:

  • This worked example assumes you are not using SQL Changeset.

    If you are using a 'check out/edit/check in' (VSS style) source control system, you can use SQL Changeset to integrate SQL Compare Professional Edition with your source control system.

    For examples and details of how to use SQL Changeset, see Using SQL Changeset

    For more information on using SQL Compare for source control, see Continuous integration for databases using Red Gate SQL tools (pdf)

  • This worked example provides two database creation scripts, one for for SQL Server 2000 and one for SQL Server 2008 and SQL Server 2005.

    The screenshots in this example use SQL Server 2008.

  • Scripts folders are only available if you are using SQL Compare Professional Edition.

1. Set up the databases

The worked example uses the following databases:

  • SprocketProduction is the production database
  • SprocketDevelopment is the modified version of the database containing the updates

To create these databases on your SQL Server:

  1. If they already exist, delete the databases SprocketProduction, and SprocketDevelopment from your SQL Server.
  2. For SQL Server 2000 users, click here to view the SQL creation script for the databases.

    For SQL Server 2008 and 2005 users, click here to view the SQL creation script for the databases.

  3. Copy the script, paste it in your SQL editor, and run it.

    The databases and their schema are created.

2. Set up the comparison

Development on the copy of the production database proceeds, and at some point a milestone is reached; the next version is ready to be tested. The development team compares the modified database with the production database schema. To ensure no untested changes are made to the production database, they save its schema as a scripts folder.

Creating a scripts folder before deployment preserves the current state of the production database. It can be checked into source control, providing history and allowing you to revert any changes.

To do this, set up a comparison project:

  1. Click [image] (New Project).

    If an unsaved project is currently open, you will be prompted to save when you click

    The Project Configuration dialog box is displayed.

  2. Under Source, select Database, and in the Database box type or select SprocketDevelopment.

    SprocketDevelopment is the new version of the database, following a development cycle.

    You must now create a scripts folder representing the schema of the production database.

  3. Under Target, select Scripts folder, then click Create schema scripts:

    The Create New Scripts Folder dialog box is displayed:

  4. Ensure that the Data source type is set to Database.
  5. Type or select SprocketProduction in Database.

    If the database is not displayed in the Database list, right-click in the Database box and click Refresh, or scroll to the top of the list and click Refresh.

  6. Click in the New folder name box. SQL Compare automatically supplies a name for the scripts folder.

    The default value is the name of the data source.

    In this example, use the default name SprocketProduction.

  7. In Create in, type the path for the folder you want to create, or click [image] Browse to browse to its location.

    If you are using a source control system, this may be the folder designated as your working folder.

  8. Click Create Scripts Folder.

    The schema is saved in the specified folder as a set of object creation script files.

    The folder you created is now shown as the target of the comparison on the Project Configuration dialog box.

  9. Click Compare Now.

    A message dialog box is displayed. If you selected the Close dialog box on completion check box last time you ran a comparison, SQL Compare closes this message dialog box automatically.

3. Select objects to synchronize

The comparison results are displayed in the main window:

To update the production database, you must create a synchronization script. Verify that all the objects you want to modify are present in the comparison results, and select them for synchronization.

To search for objects, type the search text in the Find box. SQL Compare searches object names and owner names.

In this example, search for all objects that contain "price" by typing price in the Find box.

The objects are now grouped by whether they match or do not match the find text. To clear the Find box, click the [image] button; all the objects are displayed.

You can view a side-by-side, color-coded listing of the differences in the object creation scripts, by clicking an object. For example, if you click the Sprockets table, you can see the differences for this table:

The two versions of the line are shown one on top of the other in the Line Differences bar. This is especially useful when the lines are too long to view all the text; you can see more of each line in the Line Difference bar. If the Line Differences bar is not displayed, click [image] and select Line Differences

For full details of how to use the comparison results window, see:

  • Viewing the comparison results
  • Viewing the SQL differences

In this example we will synchronize all objects.

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

4. Create a synchronization script

When you have selected the objects to synchronize, click [image] Synchronization Wizard.

1. Choose synchronization method

When a scripts folder is the target, you can:

  • Create a synchronization script to update the database from which the scripts folder was created
  • Modify files in the scripts folder directly

In this example, we will create a synchronization script. The script can then be run on the production database to update it with the development changes.

  1. Ensure that Create a synchronization script is selected
  2. Click Next

2. Review dependencies

SQL Compare checks for dependencies:

For more information, see Using the synchronization wizard

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

3. Review script

When you have reviewed the script, you can open it in your SQL editor, or save a copy of the script.

  1. Click Open Script in Editor.

    The script is opened in your default SQL editor, and can be run on the SprocketProduction database to update it with the development changes.

  2. Run the script.

    To verify that the synchronization was successful, set up a new project comparing SprocketDevelopment and SprocketProduction.

    If you have not saved the project, you will be prompted to do so when you click

    It is not necessary to save the current project.

    As the databases are now identical, All objects identical is shown in the direction bar:

See also

Using the Synchronization wizard

Viewing the comparison results

Setting project options

Setting data sources

Understanding the synchronization

Worked example: Comparing and synchronizing two databases

Working with scripts folders


Comparing databases on unconnected SQL Servers

You can compare databases on unconnected SQL Server instances by creating SQL Compare snapshots or scripts folders from the databases.

Using snapshots

To compare databases on unconnected SQL servers using snapshots:

  1. Create a snapshot of the database(s).

    For more information, see Working with snapshots

  2. Copy the snapshot to the required location.
  3. Do one of the following:
    • Create a project that compares the snapshot with a database
    • Create a project that compares the snapshot with another snapshot, or with a scripts folder.
  4. Run the comparison on the project.
  5. Select the database objects that you want to include in the synchronization.
  6. Use the Synchronization wizard to generate the synchronization SQL script.

Note that when a snapshot is the target, synchronizing creates a script to update the database from which the snapshot was created. Snapshots cannot be modified directly.

Using scripts folders

To compare databases on unconnected SQL servers using scripts folders:

  1. Create a scripts folder from the database(s).

    For more information, see Working with scripts folders

  2. Copy the scripts folder to the required location.
  3. Do one of the following:
    • Create a project that compares the scripts folder with a database
    • Create a project that compares the scripts folder with another set of scripts
  4. Run the comparison on the project.
  5. Select the database objects that you want to include in the synchronization.
  6. Use the Synchronization wizard to update the database schema, object script files or generate a synchronization script.

Note that scripts folders are only available in SQL Compare professional edition.

See also

Setting up the synchronization

Using the Synchronization wizard

Working with scripts folders

Working with snapshots


Comparing databases on different SQL Server versions

This topic provides additional information for you if you are comparing databases that are on different versions of Microsoft® SQL Server™.

Comparing a SQL Server 2005 or 2008 database with a SQL Server 2000 database

If you are updating a SQL Server 2008 or SQL Server 2005 database to match a SQL Server 2000 database, you must not change the default project options on the Project Configuration dialog box.

However, if your database is on a SQL Server with case-sensitive sort order, you must select the Treat items as case sensitive project option.

If you are updating a SQL Server 2000 database to match a SQL Server 2005 database, note that:

  • SQL Compare may be unable to synchronize all objects. Warnings are displayed where possible.
  • SQL Compare version 7.0 (and earlier) cannot decrypt objects that are encrypted in a SQL Server 2005 or SQL Server 2008 database.

    If an encrypted object which cannot be decrypted exists in both databases, it is shown under the objects that exist in both but are different group in the upper (Results) pane. SQL Compare cannot compare the encrypted objects, or display their creation scripts, and cannot synchronize them.

  • In SQL Compare version 7.1 (and later) you have the option to decrypt text objects in SQL Server 2008 and SQL Server 2005 databases that were created using the WITH ENCRYPTION option.

    Disabling this option can result in faster performance. To disable this option, on the Options tab of the Project Configuration dialog, clear the Decrypt encrypted objects on 2005 and 2008 databases check box.

    When this option is disabled, SQL Compare cannot compare the encrypted objects, or display their creation scripts, and cannot synchronize them.

  • An extra line of white space may be appended to the creation scripts of stored procedures, functions, rules, and so on. So that SQL Compare does not flag these objects as different, you should select the project option Ignore white space.

When you create a default value or constraint in SQL Server 2005, the definitions of the default value or constraint are parsed by SQL Server 2005, and the parsed version is stored. The syntax of the SQL Server 2005 parsed version is not the same as the parsed version in SQL Server 2000.

For example, in SQL Server 2005, (1) is parsed to ((1)). SQL Compare highlights these differences in the lower (SQL Differences) pane, and if these are the only differences, it shows the objects to be identical.

For more information, see Viewing the SQL differences

Comparing SQL Server 2005 compatibility level 80 databases

If a SQL Server 2005 database has its compatibility level set to 80, it conforms to strict rules for views, stored procedures, functions, and DML triggers. Therefore, comparisons and synchronizations may fail.

Comparing SQL Server 2005 compatibility level 90 databases

If a SQL Server 2008 database has its compatibility level set to 90, it conforms to strict rules for views, stored procedures, functions, and DML triggers. Therefore, comparisons and synchronizations may fail.

See also

Warnings

Setting project options


Creating a rollback script

If you want to be able to reverse a synchronization, or to return a database to a specific state, you can create a rollback script.

Before you run the Synchronization Wizard:

  1. In the main window, right click in the Direction Bar, and click Switch synchronization direction
  2. Use the Synchronization Wizard to create and save a synchronization script.

For example, if you are migrating changes from WidgetStaging (the source) to WidgetProduction (the target), switching the synchronization direction makes WidgetProduction the source, and the synchronization script you create can be run on WidgetProduction in future to restore it to its current state.

You can also roll back a synchronization by creating a snapshot when you synchronize:

On the first page of the Synchronization Wizard, select the Back up target before synchronization check box. This adds a page to the wizard where you can choose to create a schema snapshot from the target. The snapshot preserves the state of the target before the synchronization, and later can be used as a source, to restore the target to that state.

See also

Using the Synchronization wizard


Setting up the comparison

In this chapter

  • Working with projects
  • Setting data sources
  • Mapping owners
  • Setting project options


Working with projects

Whenever you compare databases, you set up a project. If you have any existing projects, the Project Configuration dialog box for your most recently used project is displayed when you start SQL Compare.

A project contains details of:

  • which data sources you selected

    If you selected a backup as a data source, the project saves details of the backup set you selected.

  • the connection details for your data sources
  • which project configuration options you selected
  • which objects you have selected for synchronization
  • your owner mappings
  • your most recently used filter

If you are using SQL Changeset and you selected a scripts folder as a data source, the project remembers if you chose to always get the latest version of the scripts from source control before comparing.

Finding a project

On the toolbar, click [image] (Open Project) to display the Projects dialog box:

The Projects dialog box shows details of your projects.

You can edit or compare these projects, or create a new project.

Creating and editing a project

To create a new project, click [image] New

To edit the current project, click [image] Edit

To open and edit an existing project, double click the project on the Projects dialog box.

Saving a project

SQL Compare does not automatically save projects.

To save a project, on the Project Configuration dialog box click Save. Alternatively, you can save a project when you are reviewing its comparison results. To do this, on the File menu click Save Project.

If there are unsaved changes in the current project, you will be prompted to save when you create a new project, open another project, or when you close the application.

Copying a project

To make a copy of a project, on the Projects dialog box, select the project, right click, and select Create Clone.

Alternatively, open the project that you want to copy, and on the Project Configuration dialog box, click Save As.

To copy a project when you are reviewing the comparison results, on the File menu, click Save Project As.

Project compatibility

You can open a SQL Compare 7, 6, or 5 project in version 8. Projects from earlier versions are not supported.

You cannot open a SQL Compare 8 project in SQL Compare 7, or in earlier versions.

If you open and save a project in SQL Compare 8, it is converted to version 8 and cannot be opened in earlier versions.

You can open a SQL Compare 8.1 (or later) project in SQL Data Compare 8.0 or later.

You can open a SQL Data Compare 8.0 (or later) project in SQL Compare 8.1 or later.

See also

Setting data sources

Setting project options


Setting data sources

When you create a new comparison project, SQL Compare requires information about which two data sources you want to compare, and how to connect to them. You enter this information on the Project Configuration dialog box.

Selecting data sources

Specify the two data sources you want to compare in the Data Sources tab. You specify a source and a target.

The source is the data source that will not change. The target is the data source that will change.

To switch the source and target, click

You can compare the following types of data source:

  • Database

    A database you can connect to on a SQL Server. You will need to enter connection details.

  • Snapshot

    A file produced by SQL Compare that contains information about the structure of a database.

    For more information, see Working with snapshots

  • Scripts Folder

    A folder containing the SQL creation script files that collectively define the database structure. This is usually organized into a number of subfolders.

    For more information, see Working with scripts folders

    You can specify a scripts folder as a data source only if you are using SQL Compare Professional edition.

  • Backup

    Native SQL Server backups or Red Gate SQL Backup backups. You can specify a backup as a data source only if you are using SQL Compare Professional edition.

    For more information, see Working with backups

You can compare any combination of data source types in your project.

If you select a snapshot or a backup as the target, the snapshot file or backup will not be modified. Instead, a synchronization script is generated to modify the database from which the snapshot or backup was made.

If you select a scripts folder as the target, you can choose to modify those script files directly during synchronization, or to create a synchronization script to modify the database from which the scripts folder was created.

Selecting a database

  1. Under Source or Target, select Database.
  2. Type or select the name of the SQL Server in the Server box.

    If you experience problems selecting a SQL Server that is not running on the LAN, for example if you are accessing the SQL Server via an Internet connection, you may need to create an alias to the SQL Server using TCP/IP (refer to your SQL Server documentation for details). You can then type the alias name in the Server box to connect to the remote SQL Server.

    To refresh the Server list, right-click the box and click Refresh, or scroll to the top of the list and click Refresh.

  3. Select the authentication method, and for SQL Server authentication enter the User name and Password.

    If you want SQL Compare to remember your password, select the Save password check box.

  4. In the Database box, type or select the name of the database.

    To refresh the Database list, right-click the box and click Refresh, or scroll to the top of the list and click Refresh.

Selecting a backup

  1. Under Source or Target, select Backup.

  2. Click Add Backup Set Files to select all the files making up the backup set you want to compare.

    To specify a network path, type the full path, including the server name, for example:

    \\ServerName\MyFolder\MyFile

    If any of the files you add are encrypted, the Decrypt Backup Files dialog box is displayed. Enter the password to decrypt these files.

To use a differential backup as a data source, you must also add the associated full backup.

Note that SQL Compare does not support using partial, filegroup, or transaction log backups as a data source.

When you have added a backup set, one of the following icons is displayed:

The backup set is valid and complete.

Select the check box to use this backup as a data source.

The backup set you have selected cannot be used as a data source.

This error is shown if the backup is corrupted, or if you have selected a partial, filegroup, or transaction log backup.

One or more files in the backup set is encrypted.

Click the padlock icon to display the Decrypt Backup Files dialog box.

Either the backup set is incomplete, or a differential backup has been added without the corresponding full backup.

Selecting a SQL Compare snapshot

  1. Under Source or Target, select Snapshot.
  2. In the Snapshot box, select the name of the snapshot, or click [image] to browse to the snapshot file.

SQL Compare displays information about the database from which the snapshot was created.

Selecting a scripts folder

  1. Under Source or Target, select Scripts Folder.

In the Scripts Folder box, select the folder, or click [image] to browse to the folder.

See also

Working with projects

Mapping owners

Setting project options

Viewing the comparison results

Working with scripts folders

Working with snapshots

Working with backups


Mapping owners

When you create a comparison project and you have selected your data sources, SQL Compare automatically maps objects with the same name and the same owner (SQL Server 2000) or schema (SQL Server 2008 and SQL Server 2005) for you.

If you want to compare objects with the same name, but those objects have different owners or belong to different schemas, you can map the owners or schemas as required. For example, if you want to compare objects in a test database that are owned by sales with objects in a production database that are owned by support, you can map sales to support.

Note that:

  • If you map the owners (or schemas) sales and support, this enables SQL Compare to compare the stored procedures [sales].[editCustomerDetails] and [support].[editCustomerDetails].

    SQL Compare can then rename other objects that are referenced by the stored procedure. For example, if the [support].[editCustomerDetails] stored procedure references the table [support].[customerDetails], the stored procedure is updated to reference the table [sales].[customerDetails]. The same applies to functions, DML triggers, DDL triggers, views, defaults, and rules.

  • If you are comparing stored procedures, functions, DML triggers, views, defaults, or rules that contain WITH elements, they are flagged as different unless the Ignore WITH element order option is selected in your comparison project.
  • You cannot perform the comparison if an existing schema or owner is not mapped. For example, if you unmap the schemas Sales and Support, and then map Support to Sales, the comparison will fail if you do not also map Sales to another schema.

Editing the owner mappings

The Owner Mapping tab of the Project Configuration dialog box enables you to edit mappings:

The upper pane displays a list of schemas, database roles, and database users that SQL Compare has automatically mapped for you; the lower pane displays a list of schemas, database roles, and database users that are not mapped.

  • To undo a mapping, in the upper pane, select the mapping, and click [image] Unmap.
  • To create a mapping, in the lower pane, select item A from the source, then select item B from the target, and click [image] Map.

    Next, select item B from the source, then select item A from the target, and click [image] Map.

    For example, to map sales to support, you create a mapping for sales to support and then you create a mapping for support to sales.

To reset the mappings to the defaults, click Restore Default Mappings.

See also

Setting data sources

Setting project options


Setting project options

The project options enable you to modify the behavior of SQL Compare. For example, you can set a project option so that SQL Compare ignores certain objects even if they are different, or so that it does not script certain properties for synchronization (such as the collation order on columns).

When you create a new project, you should run the comparison with the default options, then review your comparison results. However, if your database is on a SQL Server with case-sensitive sort order, you must select the Treat items as case sensitive option. When you have reviewed your comparison results, you may then want to consider changing some of the options.

The options you set are saved for each project, and are modified on the Project Configuration dialog box.

To display the Project Configuration dialog box, click [image] (Edit Project), or select Project Options from the Tools menu.

The options you can set are described below. Note that some of the options apply only to the comparison; they do not affect the synchronization. Similarly, some options apply only to the synchronization.

Default options

To make the current selection of options your defaults, click Save As My Defaults. These saved defaults will be used for all new projects. To restore your defaults after making changes, click My Defaults.

To reset all the options to their original settings, click Red Gate Defaults. The default options for a project are as follows:

  • Decrypt encrypted objects on 2005 and 2008 databases
  • Ignore white space
  • Ignore fill factor and index padding
  • Ignore filegroups, partition schemes, and partition functions
  • Ignore user properties
  • Ignore WITH element order
  • Ignore database and server names in synonyms

To search for an option, type search text in the Find box. The list is filtered to display only those options that contain the search text.

Use case sensitive object definition

For databases with case-sensitive collation, enables objects with case-sensitive names to be compared and synchronized. For example, considers object names such as ATable and atable as different and performs case-sensitive comparisons on stored procedures, and so on.

You should use this option only if you have databases with binary or case-sensitive sort order.

Note that you should take care when you change this option. For example, if you create a database snapshot with this option selected and you then compare the snapshot with another database with the option cleared, SQL Compare may produce unexpected errors.

Force column order

If additional columns are inserted into the middle of a table, this option forces a rebuild of the table so the column order is correct following synchronization. Data will be preserved.

Do not use transactions in synchronization SQL scripts

Removes transactions from the synchronization SQL scripts to produce SQL code that is more readable.

If this option is not selected and the synchronization script fails, the script is rolled back to the start of the failed transaction. If this option is selected, the script is not rolled back. This can be useful for detection of errors within a script.

Add WITH ENCRYPTION

Adds WITH ENCRYPTION when stored procedures, functions, views, and triggers are included in the synchronization.

Note that if you use ADD ENCRYPTION on a SQL Server 2005 database, SQL Compare will not subsequently be able to display, compare, or synchronize the encrypted objects.

When SQL Compare creates a snapshot, this option is ignored, and WITH ENCRYPTION is not saved in the snapshot.

Do not use ALTER ASSEMBLY to change CLR objects

This option is used only for SQL Server 2005 and SQL Server 2008 databases.

If CLR objects are to be synchronized, this option forces two rebuilds of the table with conversion to and from strings to update the CLR objects, instead of using ALTER ASSEMBLY. For a detailed explanation, see Understanding the synchronization.

This option affects the synchronization only.

Consider next filegroups in partition schemes

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

When this option is selected, if a partition scheme contains a next filegroup, SQL Compare considers the next filegroup in the comparison and synchronization if the partition scheme is extended. The next filegroup does not affect the way in which data is stored.

To ignore next filegroups, clear the check box.

Disable DDL triggers during synchronization

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

DDL triggers can cause problems when you run the synchronization. Select this option to disable any enabled DDL triggers before synchronizing the databases, and re-enable those triggers following synchronization.

Add database USE statement

Adds a database USE statement to the top of the synchronization SQL script.

This option affects the synchronization only.

Don't include a comment header in the synchronization script

When this option is set, the comment header is not included in the synchronization script.

Decrypt encrypted objects in 2008 and 2005 databases

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

When this option is selected, SQL Compare decrypts text objects in SQL Server 2008 and SQL Server 2005 databases which were created using the WITH ENCRYPTION option. Note that when comparing large databases, selecting this option can result in slower performance.

When this option is not selected, text objects in SQL Server 2008 and SQL Server 2005 databases are shown as different, and cannot be synchronized.

Ignore indexes

Ignores indexes when comparing and synchronizing databases.

Ignore permissions

Ignores permissions on objects when comparing and synchronizing databases.

Ignore DML triggers

Ignores DML triggers when comparing and synchronizing databases.

Ignore constraint and index names

Ignores the names of indexes, foreign keys, primary keys, and default, unique, and check constraints when comparing databases. Note that the names will not be ignored when the databases are synchronized.

Ignore white space

Ignores white space (newlines, tabs, spaces, and so on) when comparing databases. Note that white space will not be ignored when the databases are synchronized.

Ignore comments

Ignores comments when comparing views, stored procedures, and so on. Note that comments will not be ignored when the objects are synchronized.

Ignore full-text indexing

Ignores full-text catalogs and full-text indexes when comparing and synchronizing databases.

Ignore users' permissions and role memberships

When role-based security is used, object permissions are assigned to roles, not users. If this option is selected, SQL Compare compares and synchronizes object permissions only for roles, and members of roles that are roles. Users' permissions and role memberships are ignored.

Ignore STATISTICS_NORECOMPUTE property on indexes

Ignores the STATISTICS_NORECOMPUTE property on indexes and primary keys.

Ignore statistics

Ignores statistics when comparing and synchronizing databases.

Ignore foreign keys

Ignores foreign keys when comparing and synchronizing databases.

Ignore check constraints

Ignores check constraints when comparing and synchronizing databases.

Ignore identity seed and increment values

For identity properties, ignores only the identity seed and increment values when comparing databases. Note that they will not be ignored when the databases are synchronized.

Ignore fill factor and index padding

Ignores the fill factor and index padding in indexes and primary keys when comparing and synchronizing databases.

Ignore INSTEAD OF triggers

Ignores INSTEAD OF DML triggers when comparing and synchronizing databases.

Ignore bindings

Ignores bindings on columns and user-defined types when comparing and synchronizing databases. For example, sp_bindrule and sp_bindefault clauses will be ignored.

Ignore WITH NOCHECK

Ignores the WITH NOCHECK argument on foreign keys and check constraints.

Note that foreign keys or constraints that are disabled, are not ignored.

Ignore filegroups, partition schemes, and partition functions

Ignores filegroup clauses, partition schemes, and partition functions on tables and keys when comparing and synchronizing databases. Partition schemes and partition functions are not displayed in the comparison results.

Ignore extended properties

Ignores extended properties on objects and databases when comparing and synchronizing databases.

Ignore SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements

Ignores these SET statements when comparing views, stored procedures, and so on. Note that these statements will not be ignored when the databases are synchronized.

Ignore collations

Ignores collations on character data type columns when comparing and synchronizing databases.

Ignore certificates, symmetric keys, and asymmetric keys

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

SQL Server severely restricts access to certificates, symmetric keys, and asymmetric keys. Consequently, SQL Compare cannot compare all of the properties for a symmetric key.

If certificates, symmetric keys, and asymmetric keys are selected for synchronization, only the permissions are synchronized.

Ignore DML trigger order

DML triggers can have an order specified, such as FIRST INSERT, LAST UPDATE, and so on. Select this option to ignore the trigger order for DML triggers when comparing and synchronizing databases. Note that the DDL trigger order is not affected.

Ignore event notification on queues

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

Ignores the event notification on queues when comparing and synchronizing databases.

Ignore user properties

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

If this option is not selected, SQL Compare compares user properties, such as the type of user (SQL, Windows, certificate-based, asymmetric key based) and any schema. If a user is selected for synchronization, SQL Compare synchronizes the properties where possible.

If you select this option, users' properties are ignored, and only the user name is compared and synchronized.

Ignore WITH element order

If a stored procedure, user-defined function, DDL trigger, DML trigger, or view contains multiple WITH elements (such as encryption, schema binding, and so on), select this option to ignore the order of the WITH elements when comparing and synchronizing databases.

Ignore LOCK properties of indexes

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

Ignores index PAGE LOCK and ROW LOCK properties when comparing and synchronizing databases.

Ignore replication triggers

Ignores replication triggers when comparing and synchronizing databases.

Ignore NOT FOR REPLICATION

Ignores the NOT FOR REPLICATION option on foreign keys, identities, check constraints and triggers.

If you select this option, the NOT FOR REPLICATION statement will not be displayed in the object creation script for foreign keys, identities, and check constraints.

In the case of triggers, the NOT FOR REPLICATION statement will be displayed in the object creation script, but will be ignored for the purposes of the comparison. When comparing triggers, you should also select the Ignore white space option, but note that this option will also be applied to all objects in the comparison.

Check constraints and foreign keys that contain the NOT FOR REPLICATION statement in their definition will automatically be flagged as WITH NOCHECK. Use the Ignore WITH NOCHECK option to identify these objects as being the same; but note that this will apply to constraints in all objects.

Ignore identity property on columns

Ignores the identity property on columns when comparing databases. Note that the identity property will not be ignored when databases are synchronized.

Ignore data compression

This option is used only for SQL Server 2008 databases.

Ignores page and row compression for tables and indexes. Note that when Ignore filegroups is selected, compression is automatically ignored for partitioned tables.

Ignore database name in synonyms

Ignores database names in synonyms when comparing databases.

Ignore owner authorization on schema objects

This option is used only for SQL Server 2008 and 2005 databases.

Ignores authorization clauses on schema-qualified objects when comparing and synchronizing databases.

See also

Working with projects

Understanding the comparison results

Understanding the synchronization


Reviewing the comparison results

In this chapter

  • Viewing the comparison results
  • Viewing the SQL differences
  • Using filters
  • Understanding the comparison results
  • Generating a report
  • Printing the comparison results


Viewing the comparison results

When you have compared the data sources, SQL Compare displays the comparison results in the upper (Results) pane. The upper pane displays all the objects you can select for synchronization.

To compare the data sources again using the same project configuration, and update the comparison results, click [image] Refresh

You can define which objects are displayed by setting up a filter. You can also view the differences in the objects' creation scripts in the SQL Differences pane.

Objects are displayed in groups, either by how they differ between the two data sources, by the object type, or by whether they match what is typed in the Find box. When you first run the comparison, the objects are grouped by Type of difference.

Object groups

To view the objects in a group, click [image], or click the group heading.

When objects are grouped by Type of difference, the Type column indicates the difference:

objects that exist in both data sources but are different

objects that exist only in the Source

objects that exist only in the Target

objects that exist in both data sources and are identical

You can also group by object type. In the Group by box, select Type of object.

When objects are grouped by Type of object, the Type column indicates the type of object.

For SQL Server 2000 and later:

Tables | Roles
Views | Rules
Stored Procedures | Defaults
Functions | User Defined Types
Users | Full Text Catalogs

For SQL Server 2008 and SQL Server 2005:

Assemblies | Queues
Asymmetric Keys | Routes
Certificates | Schemas
Contracts | Services
DDL Triggers | Service Bindings
Event Notifications | Symmetric Keys
Message Types | Synonyms
Partition Functions | XML Schema Collections
Partition Schemes | Full Text Stoplist (SQL Server 2008 only)

The Owner column displays the owner (or schema) of the object.

Note that this column is shown only when there is more than one owner (or schema) in the database.

Selecting objects for synchronization

The grouping bars show the number of objects in a group that will be synchronized.

Use the check boxes in the middle of the upper pane to include an object in the synchronization or exclude it from the synchronization. If only some objects in a group are selected for synchronization, the check box on the grouping bar enters a mixed state:

Note that the check box for an object grouping affects all objects in that grouping. So, if you save a project with a grouping set to include all objects in that group, the next time you use this project, all objects in that group are selected for synchronization, even if they did not exist when the project was last used.

Finding an object

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 Compare searches object names and owners (schemas).

Note that the search is not case-sensitive.

To clear the Find box click the [image] button.

See also

Using filters

Generating a report

Printing the comparison results

Setting up the synchronization


Viewing the SQL differences

The lower (SQL Differences) pane displays a side-by-side listing of differences in the creation SQL script for an object. To display the SQL Differences pane, click an object in the upper (Results) pane.

You can adjust the height of the SQL Differences by dragging it up or down, or by using the [image] buttons to move the pane to a fixed height.

Click [image] to open the SQL Differences pane in a separate window.

Viewing differences

Lines that contain differences are displayed with a shaded background; text within a line that is different is displayed with a darker shaded background.

The central column shows the type of difference:

there are differences in the group of SQL statements

this line of SQL will be added if you synchronize this object (the direction of the arrow indicates the database that will be updated)

this line of SQL will be added if you synchronize this object (the direction of the arrow indicates the database that will be updated)

this line of SQL will be removed if you synchronize this object

There is no icon if the two lines are identical.

Use [image] Next and [image] Previous to go to lines that contain a difference. You can also use Alt + Up Arrow and Alt + Down Arrow.

The Line Differences bar

Click [image] and select Line Differences to display the Line Differences bar. The two versions of the line are shown one on top of the other. This is especially useful when the lines are too long to view all the text:

Scroll bar

Colored blocks in the left scroll bar indicate the location in the script of lines that are different.

A line of SQL that exists only in the object script on the left.

A line of SQL that exists only in the object script on the right.

A line of SQL that exists in both object scripts, but is different.

Searching and copying the creation script

To search the SQL statements, right-click, and click Find.

To search the SQL statements in the other data source, click Find on this side.

The search is not case-sensitive.

Viewing the object synchronization script

To view the synchronization script for the selected object, right-click, and click Show Object Synchronization Script.

The Single Object Synchronization Script dialog box is displayed.

You are recommended to synchronize only using the full synchronization script created by the Synchronization Wizard, as the single objects script cannot account for dependencies. If you use the object synchronization script to synchronize a single object, there may be unexpected results, or the synchronization may fail.

To view the full synchronization script that will make the two databases identical, use the Synchronization wizard.

See also

Setting up the synchronization

Printing the comparison results

Viewing the comparison results

Generating a report

Using filters


Using filters

You can use filters to define which objects are displayed in the upper (Results) pane. For example, you may be interested in only tables, or in only some of the stored procedures owned by a specific schema. You can set up a filter to show you only the objects matching these conditions.

You can set up a filter for object types, and edit the filter rules to restrict which specific objects are included in or excluded from the upper pane.

The default filter is Nothing Excluded. If you edit this filter, Custom* is shown in the Filter box. An asterisk is displayed next to the name of any filter you edit, to show that there are unsaved changes.

You can save your filters for use in multiple projects. To choose a different filter, in the Filter box select the filter you want to use; if the filter is not listed, select Open filter from file and browse to the filter.

Filter rules

You can define which object types the filter includes in the comparison results using the object type check boxes in the Filter pane.

You can also create rules to control the specific objects that a filter includes or excludes. To edit the filter rules:

On the Filter pane, click [image] Edit Filter Rules.

The Edit Filter Rules dialog box is displayed:

You can select individual object types or All object types in all schemas, and edit the rules applying to those objects.

When you create a filter rule, its conditions are displayed under the name of any object types it applies to.

To edit the filter rule for an object type, move the mouse pointer over it and click the [image] (Edit) button that becomes available:

To clear the filter rule for an object type, move the mouse pointer over it and click the [image] (Clear) button that becomes available.

You can clear all current filter rules by selecting the filter Nothing Excluded from the Filter box.

Saving and deleting filters

Saved filters are available in subsequent projects, and can be selected from the Filter box. Saved filters have the file extension .scpf

  • To save the current filter, on the Filter pane, click [image] Save

    In the Save Filter dialog box, type the name for the filter.

    When you save an edited filter, you can either save it with the same name to overwrite it, or change the name to create a new filter.

  • To delete the current filter, on the Filter pane, click [image] Delete

    You will be prompted to confirm. When you delete the current filter, Custom* is shown in the Filter box, and the conditions for the filter you just deleted remain set until you select another filter or make any changes.

Note that any unsaved changes to a filter are lost when you select another filter.

Filters and Find

A filter defines which objects are displayed in the upper (Results) pane; using Find changes how those objects are grouped. So, if an object matches the term in the Find box, and is excluded by a filter, it is not displayed in the upper pane.

Find groups results by whether they match or do not match the term typed in the Find box. Objects not matching the find term are still displayed in the upper pane, although this group is shown as collapsed by default.

Only objects displayed in the upper pane can be selected for synchronization.

Example: excluding objects by type

If you want to view comparison results for only Tables and Views:

  1. On the Filter pane, select and clear the All object types included check box.

    This ensures that no objects are displayed in the upper (Results) pane.

  2. Select the View and Table check boxes.

The filter is applied. Only tables and views are displayed in the upper pane.

Example: Excluding objects from a specific schema

If you want to view comparison results that exclude any objects owned by the schema Marketing:

  1. On the Filter pane, click the [image] Edit Filter Rules button.

    The Edit Filter Rules dialog box is displayed.

  2. In the For box, ensure that All object types in all schemas is selected.
  3. In the [image] box, select Exclude if.
  4. Under Property, select Schema name.
  5. Under Operator, select Equals.
  6. Under Value, type Marketing.
  7. Click OK.

The filter is applied. No objects owned by the schema Marketing are displayed in the upper pane.

Example: including only those tables with a specific name or a particular owner

If you want to view comparison results for all the tables that have names beginning with Marketing, as well as all those owned by the schema Marketing, regardless of their names:

  1. On the Filter pane, select and clear the All object types included check box.

    This ensures that no objects are displayed in the upper pane.

  2. Click the [image] button for Table.

    The Edit Filter Rule dialog box is displayed.

  3. In the [image] box, select Include if.
  4. Under Property, select Object name.
  5. Under Operator, select Begins with.
  6. Under Value, type Marketing.
  7. Click

    A new OR condition becomes available.

  8. Under Property, select Schema name.
  9. Under Operator, select Equals.
  10. Under Value, type Marketing.
  11. Click OK.

The filter is applied. Only objects owned by the schema Marketing, or with names that begin with Marketing are displayed in the upper pane.

Filters and synchronization

The current filter affects which objects you can select for synchronization:

  • When you use the filter to exclude an object or object type, it is removed from the upper pane and cannot be selected for synchronization.
  • If you select an object for synchronization and later set up a filter that excludes it, the object is not synchronized.

Objects excluded by the filter are only included in the synchronization as dependencies. If an excluded object is referenced by an object you selected for synchronization, you will be notified of this dependency, and can choose to synchronize the affected object on the Review Dependencies page of the Synchronization Wizard.

See also

Viewing the comparison results

Printing the comparison results

Setting up the synchronization


Understanding the comparison results

This topic provides information that may help you to understand your comparison results.

Objects with different owners or schemas

Objects that are the same but have different owners or schemas are treated as different objects. For example, if a stored procedure exists in both databases and is identical except for its owner, it is considered to be a completely different object.

If you want to compare objects that are the same but have different owners, you can remap the owners when you set up your comparison project.

Database-level permissions

For SQL Server 2000, differences in database-level permissions are not detected by SQL Compare. For example, if you have used SQL Server Enterprise Manager to set up permissions for your database, such as GRANT CONNECT or GRANT BACKUP, those permissions are not considered; however, permissions on objects are detected. If you want to include database-level permissions in your comparison project, it is recommended that you use roles.

For SQL Server 2008 and SQL Server 2005, differences in database-level permissions are detected.

Object-level permissions

When you connect to a database for which your user is not the database owner, SQL Compare displays only those objects for which you have been granted permissions. In addition, the SQL Differences pane will display only the object-level permissions for the authenticated user; other users' permissions will not be displayed.

Note that the synchronization script may fail if the authenticated user does not have full object-level permissions.

If a permission is given using WITH GRANT OPTION (for example GRANT SELECT ON <objectname> TO <username> WITH GRANT OPTION) it will not be recognized. Permissions assigned that do not use WITH GRANT OPTION are detected.

Extended stored procedures

SQL Compare does not compare extended stored procedures.

Encrypted database objects

If you are comparing a SQL Server 2000 database that contains an encrypted user-defined function, stored procedure, trigger, or view and you have system administrator permissions, SQL Compare decrypts the object and you can view its internal SQL in the SQL Differences pane of the main window.

In SQL Compare version 7.1 (and later) you have the option to decrypt text objects in SQL Server 2008 and SQL Server 2005 databases created using the WITH ENCRYPTION option.

Disabling this option can result in faster performance. To disable this option, on the Options tab of the Project Configuration dialog box, clear the Decrypt encrypted objects on 2005 and 2008 databases check box.

When this option is disabled, SQL Compare cannot compare the encrypted objects, or display their creation SQL scripts, and cannot synchronize them.

SQL Compare version 7.0 (and earlier) cannot decrypt objects that are encrypted in a SQL Server 2005 or SQL Server 2008 database. If an encrypted object which cannot be decrypted exists in both databases, it is shown under the objects that exist in both but are different group in the comparison results in the main window (select Type of Difference in the Group by box to arrange objects by difference). SQL Compare cannot compare the encrypted objects, or display their creation SQL scripts, and cannot synchronize them.

Certificates, symmetric keys, and asymmetric keys

SQL Server severely restricts access to certificates, symmetric keys, and asymmetric keys. Consequently, SQL Compare cannot compare all of the properties for a symmetric key.

To ignore all certificates, symmetric keys, and asymmetric keys in the synchronization, on the Project Configuration dialog box, select the project option Ignore certificates, symmetric and asymmetric keys.

Replication

SQL Compare does not compare stored procedures that have been created for replication.

See also

Understanding the synchronization

Setting up the synchronization

Viewing the comparison results


Generating a report

When you have run the comparison, you can export the results as a report. You can choose to include the objects that are selected for synchronization, or all objects with differences, and optionally, identical objects.

To generate a report, on the Tools menu, click Generate Comparison Results Report.

Note that the report groups objects according to whether they will be updated, created or dropped, or are identical. Selecting Type of object or No groups in the Group by box in the main window is not reflected in the report.

You can create a report in one of four formats:

Interactive HTML

You can expand and collapse the object groups in the Interactive HTML report. By clicking an object you can view the differences in the object creation scripts.

Simple HTML

The view SQL links in the Simple HTML report enable you to view the differences in the creation script for an object. The Summary links enable you to locate an object in the object groupings.

Excel

An Excel report does not show object creation scripts or differences.

XML

The XML report is an XML file listing the objects you selected, their creation scripts, and whether they are different, identical, or present in only one of the data sources. This is useful, for example, if you want to apply your own formatting using XSLT.

See also

Viewing the comparison results

Viewing the SQL differences

Using filters

Understanding the comparison results

Printing the comparison results

Setting up the synchronization

Viewing the comparison results


Printing the comparison results

When you have run the comparison, you can print a summary of the results. You can use filters or the Find box to define the object types that are printed.

The summary groups the objects to be printed using the selected option in the Group by box in the main window. You can change the Group by option, for example to show objects by type, before printing.

To see a preview of the summary, on the File menu, click Print Preview.

The Print preview dialog box provides the following options:

prints the summary

zooms in, zooms out, or applies a zoom factor

displays the first page, previous page, next page, or last page

To specify the page setup, on the File menu, click Page Setup.

See also

Viewing the comparison results

Using filters

Generating a report


Synchronizing data sources

In this chapter

  • Setting up the synchronization
  • Using the Synchronization wizard
  • Backing up before synchronization
  • Warnings
  • Understanding the synchronization


Setting up the synchronization

When you have reviewed the comparison results, you can run the Synchronization Wizard to create the SQL script to synchronize selected objects:

  1. Select the objects that you want to synchronize.
  2. Click [image] Synchronization Wizard to start the Synchronization wizard.

Selecting the objects to synchronize

By default, no objects are selected for synchronization. You can modify the selection by using the check boxes in the middle of the upper (Results) pane:

Object selections are remembered when you save a project.

To select all the objects in a group for synchronization, select the check box on the grouping bar for that group; alternatively, you can right-click within the group and click Include All Objects in this Group.

To exclude all objects displayed in a group from the synchronization, clear the check box; or, you can right-click the group and click Exclude All Objects in this Group.

The top level check box in the central column enables you to include or exclude all objects that are displayed in the upper (Results) pane:

You can filter the object types and use the Find box to assist you with your selection.

Note that if you select an object for synchronization and then set up a filter that excludes that object type, the object is not included in the synchronization. Only selected objects that are shown in the comparison results are included.

For example, if you select TableA for synchronization, and then set up a filter to exclude all tables, no tables are shown. If you subsequently remove the filter, TableA is displayed and remains selected for synchronization.

Example: Selecting objects that exist only in the source

  1. In the Filter pane, to the left of the results, ensure that the default filter Nothing Excluded is selected, so that all the objects are displayed in the upper (Results) pane.

    No objects are now excluded by the filter.

  2. Ensure the top level check box in the central column is cleared, or on the Actions menu select Exclude All.

    No objects are now selected for synchronization.

  3. From the Group by box, select Type of difference if it is not already selected. On the grouping bar for objects that exist only in <source database name>, select the check box to include all of the objects in this group.

Example: Selecting only stored procedures

  1. From the Group by box, select Type of difference if it is not already selected.

    The objects are now grouped by whether they are different, identical, or present in only one of the data sources.

  2. In the Filter pane, select and clear the All object types included check box, and then select the check box for Stored Procedures.

    Only Stored Procedures are now displayed in the upper pane.

  3. Select the top level check box in the central column to include all objects in synchronization.

Example: Selecting only tables that exist in both databases but are different

  1. From the Group by box, select Type of difference if it is not already selected.

    The objects are now grouped by whether they are different, identical, or present in only one of the data sources.

  2. In the Filter pane, clear the All object types included check box, and then select the check box for Tables.

    Only Tables are now displayed in the upper pane.

  3. Clear the top level check box in the central column to exclude all objects from synchronization.

    No objects are now selected for synchronization.

  4. On the grouping bar for objects that exist in both but are different, select the check box to include all of the tables in this group in synchronization.

See also

Understanding the synchronization

Using filters

Viewing the comparison results

Using the Synchronization wizard


Using the Synchronization wizard

When you have selected the objects that you want to include in the synchronization, use the Synchronization Wizard to create the SQL script that will synchronize the databases.

To open the Synchronization Wizard, click [image] Synchronization Wizard

There are four possible steps in the wizard:

  1. Choose synchronization method

    Create and save a script, perform the synchronization from SQL Compare, or update a scripts folder.

  2. Review dependencies

    View a list of any objects that reference or are referenced by those you have chosen to synchronize. You can choose to synchronize these objects.

  3. Configure Backup (optional step)

    If, on the first page of the wizard, you selected the option to back up before synchronizing, this additional step is shown. If you do not choose to back up, this step is not part of the wizard.

  4. Review

    View the synchronization script, review a summary of the synchronization actions, and see information about any warnings.

Synchronizing backups

When you have selected a backup as the target, the synchronization wizard creates a script to update the database from which the backup was created. Backups cannot be modified directly.

When a backup is the source, and a database is the target, the database can be synchronized with the backup.

For more information, see Working with backups

Synchronizing snapshots

When a SQL Compare snapshot is the target, synchronization creates a script to update the database from which the snapshot was created. Snapshots cannot be modified directly.

When a snapshot is the source, and a database is the target, the database can be updated to synchronize it with the snapshot.

This is useful, for example, if you want to roll back changes. If you have made changes to a database, and created a snapshot before synchronization, you can then set the snapshot as the source, and the database as the target, to roll back the changes.

For more information, see Working with snapshots

Synchronizing scripts folders

When a scripts folder is the target, you can either:

  • Create a synchronization script to update the database from which the scripts folder was created, or
  • Update files in the scripts folder directly

    Note that when an object is dropped during synchronization, its script file is not deleted.

If a scripts folder is the target, and any of the script files that will be modified are designated as read-only, a warning is displayed. If you click Yes to continue, these files will be made writable so that they can be modified. This may occur, for example, when you are working with a source control system that sets files to read-only status in some situations.

For more information, see Working with scripts folders

Additional information

Note that:

  • If you are using SQL Changeset to integrate SQL Compare Professional Edition with your source control system, you will see the Check files out from source control before modifying check box.

    For more information, see Saving modifications to source control.

  • If SQL Compare is unable to synchronize the data sources, an error message is displayed, and where possible all changes are rolled back.

    However, if you have selected the project option Do not use transactions in synchronization SQL scripts, the changes are not rolled back.

1. Choose synchronization method

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

Create a synchronization script

If you choose to create a synchronization script, on the Review page you can save a copy of the script or open it in your SQL editor.

To change the application you use to open the script, click Change. The Application Options dialog box is displayed, and you can specify the default application used to edit SQL Scripts.

Synchronize using SQL Compare

If you choose to synchronize using SQL Compare, on the Review page you can save a copy of the script and exit the wizard, or click Synchronize Now to perform the synchronization.

If the target is a scripts folder, this option is instead replaced by Update the scripts folder, and the SQL script files in the target are modified when you synchronize.

If you select Back up target before synchronization, a step is added to the Synchronization Wizard allowing you to specify details of the backup.

Note that if the target is a backup or a snapshot, the option to synchronize using SQL Compare is not available. Instead, synchronizing creates a script to modify the data source form which the snapshot or backup was created.

2. Review dependencies

When you run the Synchronization Wizard, SQL Compare checks for dependencies:

All the objects you selected for synchronization are listed. If they reference or are referenced by objects you did not select, and these dependencies affect the synchronization, they are listed on this page.

For example, if a stored procedure references a table, and you do not include that table in the synchronization, the table is listed here.

By default, SQL Compare includes these objects in the synchronization. To exclude them, clear the Synchronize all dependencies check box.

You are recommended to synchronize all objects with dependencies. If you do not, objects may be left in an invalid state, or the synchronization may fail.

3. Configure backup (optional step)

If you selected the option to back up before synchronization, the Configure backup page is added to the Synchronization Wizard.

This page allows you to create a SQL Compare schema snapshot, or a full backup. You can use either Red Gate SQL Backup, or native SQL Server to perform the backup.

If the target is a scripts folder, you cannot perform a full backup before synchronization. You can only create a snapshot from a scripts folder.

Create a SQL Compare snapshot

A SQL Compare schema snapshot is a binary file containing information about the structure of a database; it does not contain any table data.

This is useful, for example, if you want to roll back changes

For more information, see Working with snapshots.

Perform a full backup

When the target is a database, you can perform a full backup. In the Back up using box , select:

  • Native SQL Server to create a backup using the native SQL Server BACKUP command
  • Red Gate SQL Backup to create a backup using SQL Backup version 4 or later

    Note that to use Red Gate SQL Backup, you must have the SQL Backup server components installed on the SQL Server instance of the target database.

For more information, see Backing up before synchronization

4. Review

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

If you are updating or creating a scripts folder, the Files tab lists the object creation and data script files modified or created during synchronization.

When you have reviewed the script, click Synchronize Now to perform the synchronization.

See also

Setting up the synchronization

Understanding the synchronization

Backing up before synchronization

Warnings


Backing up before synchronization

The Configure Backup page of the Synchronization Wizard allows you to perform a full backup using either Red Gate SQL Backup, or SQL Server native backups.

Backup name and location

For SQL Server native:

  1. Type the file path in the Backup folder box or click [image] to specify the path using the folder browser. By default, Backup folder is set to the default backup folder for the SQL Server instance.
  2. Type the file name in the box to the right of the Backup folder box.

For Red Gate SQL Backup:

  1. Type the file path in the Backup folder box or click [image] to specify the path using the folder browser. By default, Backup folder is set to the folder specified in the SQL Backup options for the SQL Server instance. If no backup file locations have been set up, SQL Backup uses the SQL Server instance's default backup folder.
  2. Specify the file name in the box to the right of the Backup folder box. By default, the file name is set to <AUTO>.sqb; SQL Backup uses the SQL Backup options to generate the backup file path and file name. If no backup file locations have been set up, SQL Backup uses the SQL Server instance's default format for file names.

    To change the file name, clear the Name file automatically check box, and type the required file name. You can use SQL Backup tags, if required. For information about tags, see File Location Tags in the SQL Backup online help.

To specify a network path in the Backup folder box, type the full path, including the server name, for example \\ServerName\MyFolder

Note that the file path is relative to the selected SQL Server. For example, if you have chosen to back up a database on a remote SQL Server instance called ServerA and you specify a local path such as C:\Backups, the backup files will be created on the C: drive on ServerA, not on the local computer.

Select the Overwrite existing backup files of the same name check box if you want to overwrite any files of the same name that exist for the file path you specified in the Backup Folder box. Note that if a file of the same name exists already and you have not chosen to overwrite it, the backup will fail if you are using SQL Backup.

Backup compression (Red Gate SQL Backup only)

If you are using SQL Backup to back up the target database, you can choose from the three compression levels described below. Generally, the smaller the resulting backup file, the slower the backup process.

To compress the backup, select the Compress backup check box and select the compression level by moving the slider.

  • Compression level 3

    Compression level 3 uses the zlib compression algorithm. This compression level generates the smallest backup files in most cases, but it uses the most CPU cycles and takes the longest to complete.

  • Compression level 2

    This compression level uses the zlib compression algorithm, and is a variation of compression level 3.

    On average, the backup process is 15% to 25% faster than when compression level 3 is used, and 12% to 14% fewer CPU cycles are used. Backup files are usually 4% to 6% larger.

  • Compression level 1

    This is the default compression level. It is the fastest compression, but results in larger backup files. On average, the backup process is 10% to 20% faster than when compression level 2 is used, and 20% to 33% fewer CPU cycles are used. Backup files are usually 5% to 9% larger than those produced by compression level 2. However, if a database contains frequently repeated values, compression level 1 can produce backup files that are smaller than if you used compression level 2 or 3. For example, this may occur for a database that contains the results of Microsoft SQL Profiler trace sessions.

Note that if SQL Backup Lite is installed on the SQL Server, you can choose only compression level 1.

Backup encryption (Red Gate SQL Backup only)

If you are using SQL Backup to back up the target database, you can encrypt the backup by selecting the Encrypt backup check box, then typing a password for the backup in Password, and again in Confirm.

If SQL Backup Pro is installed on the SQL Server, you can choose 128-bit or 256-bit encryption; if SQL Backup Standard is installed on the SQL Server, you can choose only 128-bit encryption; if SQL Backup Lite is installed on the SQL Server, you cannot encrypt the backup.

You must remember your password; if you do not, you will not be able to access the encrypted backup.

Using multiple threads (Red Gate SQL Backup only)

If you are using SQL Backup to back up the target database and you are using a multi-processor system, using multiple threads can speed up the backup process. Select the Use multiple threads check box and type or select the number of threads up to a maximum of 32. You are recommended to start with one thread fewer than the number of processors. For example, if you are using four processors, start with three threads.

For details of how you can find out the most effective number of threads to use for your setup, see Optimizing Backup Speed in the online help for SQL Backup.

See also

Setting up the synchronization

Using the Synchronization wizard

Warnings

Understanding the synchronization


Warnings

When the Synchronization Wizard displays the Review page, you can click the Warnings tab to view any warnings about inefficiencies in the script, or reasons the script may fail.

The most common warnings that SQL Compare may display are summarized below. SQL Compare may also display warnings specific to SQL Server 2008 and SQL Server 2005 databases.

Table rebuild

To rebuild a table, SQL Compare drops the table and recreates it. SQL Compare creates a temporary table to store data so that data is not lost when the table is dropped.

SQL Compare rebuilds a table when:

  • a table's filegroup has changed

    You can ignore filegroups by selecting the Ignore filegroups, partition schemes, and partition functions project option. By default, filegroups are ignored.

  • a column cannot be altered

    For example, this warning is displayed if a column is to be changed from data type text to varchar.

  • a property of a column cannot be altered

    For example, this warning is displayed if a column is an identity in one database but not in the other.

  • the identity column on a table has changed

    For example, this warning is displayed if the seed has changed.

  • the column order on a table has changed

    This warning is displayed only if you select the Force column order in your project options and the order of columns in a table has changed. To ignore column order, clear the project option.

  • column x on table y must be added but has no default and does not allow NULL values

    If the table contains data, the synchronization script will not work. To avoid this, add a default to the column in the source database, or set it to allow NULL values.

  • column x on table y must be added and does not allow NULL values; the default z must be bound to it

    A table in the source database contains an additional column that is set to NOT NULL, and has a default set.

Non-standard filegroups

You must create the filegroups manually before you perform the synchronization. SQL Compare lists the filegroups that must be created.

  • You can ignore filegroups by selecting the Ignore filegroups, partition schemes, and partition functions project option. By default, filegroups are ignored.

Column will be truncated

For example, SQL Compare displays this warning when the length that is defined for the column has changed (such as varchar(50) to varchar(30)).

This may result in loss of data.

Invalid cast

For example, SQL Compare displays this warning when:

  • a data type has changed from text to varchar
  • a user-defined data type has changed

In these cases, the synchronization may fail.

Loss of precision or data

For example, SQL Compare displays this warning when the precision or scale of a decimal column has changed.

There may be loss of precision or data, or the synchronization may fail.

Default password

If a user or application role must be created, SQL Compare displays details of the user or application role, and the default password that will be applied. The default password is p@ssw0rd.

Corrupt login

SQL Compare displays this warning when the login for a user is not defined. For example, when a database is restored, the association between users and logins is not preserved. To link a user to a login, you use sp_change_users_login. For more information, refer to your SQL documentation.

Statistics creation

Statistics will be created with default settings. You may need to modify the statistics manually.

No default value for column

The default value that is set in the source database will be applied where appropriate. If there is no default value, the update may fail.

Full-text information is being added to the database

The target database may not be full-text enabled (for example, because it has recently been restored from a backup). Ensure any full-text catalogs have been rebuilt on the target database before you run the script.

You can ignore full-text indexing by selecting Ignore full text indexing in your project options.

See also

Using the Synchronization wizard

Understanding the synchronization


Understanding the synchronization

This topic provides information that may help you to understand the behaviour of the synchronization script.

Column order

Column order is not forced unless you select the Force column order project option.

For example, your source database has a table that contains ColA and ColB, in that order, and your target database has the same table but with ColB then ColA. If Force column order is not selected, SQL Compare shows the tables as identical objects in the comparison results. If the option is selected, SQL Compare shows the columns as different objects; you can select the objects for synchronization.

When column order is to be changed in a database, SQL Compare provides a warning in the Summary page of the Synchronization Wizard to notify you that the table will be rebuilt. SQL Compare uses temporary tables to ensure that any data in the table is not lost.

Renamed columns

SQL Compare attempts to recognize renamed columns by the similarity of the names and the data types of the columns. When a renamed column is recognized as such, SQL Compare renames the column as appropriate.

However, if the names and data types are very different, SQL Compare may consider the renamed column to be a completely different column. In this case, if ColA in your source database is renamed to ColB in your target database, when SQL Compare creates the synchronization SQL script, ColA will be created in the target database as a new column and ColB will be deleted. To avoid data loss, before you synchronize the databases you must take care to preserve any data in the two columns, and merge them following the synchronization.

Renamed objects

SQL Compare will detect inconsistencies in SQL Server when the name of an object such as a stored procedure, view, or function has been changed using sp_rename. In SQL Server, using sp_rename does not change the corresponding name in the object definition. SQL Compare will fix this inconsistency if the object needs to be altered by editing the name within the object definition to match the object name.

It is not considered best practise to use sp_rename to rename stored procedures, triggers, user-defined functions, or views.

Updated views

If your views have not been updated by the synchronization script and they contain a SELECT * statement, you must refresh them using sp_refreshview, to reflect any changes that have been made to the underlying objects on which the view depends. Refer to your SQL Server documentation for more information.

Note that it is not best practice to use SELECT * statements in views; it is recommended that you specify an explicit column list.

Database diagrams

SQL Compare does not compare or synchronize database diagrams.

System objects

SQL Compare does not compare or synchronize system objects, except for users, roles, and system schemas.

Replication

If objects that are used in replication are synchronized, errors may occur. For example, SQL Compare cannot drop a table if it is used for replication.

Users

In Microsoft® Windows®, users are a composite of the domain name or computer name and the user name, for example Computer1\WindowsUser1. SQL Compare references only the user name, so that Computer1\User1 and Computer2\User1 would be considered as the same. Therefore, if you intend to synchronize users, ensure that their user names are different.

SQL Compare compares and synchronizes changes to users, such as changes to permissions. However, SQL Compare does not compare or synchronize modifications to user passwords.

Filegroups

SQL Compare supports the synchronization of databases that use multiple filegroups. However, you must ensure that the filegroups have been created on the target server prior to synchronization. If the filegroups do not exist, the synchronization will fail.

When a filegroup is to be changed in a database, SQL Compare provides a warning in the Synchronization script page of the Synchronization Wizard to notify you that the table will be rebuilt. SQL Compare uses temporary tables to ensure that any data in the table is not lost.

Encrypted database objects

If you are synchronizing a SQL Server 2000 database that contains an encrypted user-defined function, stored procedure, trigger, or view and you have system administrator permissions, SQL Compare decrypts the object and you can view its internal SQL in the synchronization SQL script. If you do not have system administrator privileges, you cannot synchronize the encrypted object.

In SQL Compare version 7.1 (and later) you can decrypt text objects in SQL Server 2005 and SQL Server 2008 databases created using the WITH ENCRYPTION option.

Disabling this option can result in faster performance. To disable this option, on the Options tab of the Project Configuration dialog box, clear the Decrypt encrypted objects on 2005 and 2008 databases check box.

When this option is disabled, SQL Compare cannot compare the encrypted objects, or display their creation SQL, and cannot synchronize them.

SQL Compare version 7.0 (and earlier) cannot decrypt objects that are encrypted in a SQL Server 2005 or SQL Server 2008 database. If an encrypted object that cannot be decrypted exists in both databases, it is shown under the objects that exist in both but are different group in the comparison results in the main window (select Type of Difference in the Group by box to arrange objects by difference). SQL Compare cannot compare the encrypted objects, or display their creation SQL, and cannot synchronize them.

CLR assemblies

When a CLR assembly is to be updated, if possible SQL Compare achieves this by using ALTER ASSEMBLY.

If SQL Compare determines that it would not be possible to use ALTER ASSEMBLY, any table that contains a CLR type from the updated assembly is rebuilt twice:

  • in the first rebuild, the CLR type columns are converted to nvarchar

    The CLR type columns are dropped and recreated.

  • in the second rebuild, the nvarchar data is converted to the final CLR type

Data is preserved. Note that the ToString representation of the CLR user-defined type must be the same for both the old and the new assembly, otherwise the synchronization script may fail, or the data may be corrupted.

To force SQL Compare to use the double table-rebuild method, select the Do not use ALTER ASSEMBLY to change CLR objects project option.

Partition schemes and partition functions

In SQL Server 2008 and SQL Server 2005, partition schemes can be specified for tables so that the table is stored in several filegroups. By default, SQL Compare ignores filegroups. However, if you clear the project option Ignore filegroups, partition schemes, and partition functions, SQL Compare synchronizes the files. Note that for updates to partition schemes, a large amount of disk space may be required on the defined filegroups because partition ranges must be merged and split.

In certain cases, for example when a partition function changes from left range to right range, it is necessary to drop and recreate partition functions and partition schemes. In these cases, the table is rebuilt twice:

  • in the first table rebuild, the content is saved to a temporary filegroup
  • in the second table rebuild, the table is migrated from the temporary filegroup to a new partition scheme

Data is preserved. Note that if a CLR assembly synchronization also requires a table to be rebuilt twice, the CLR assembly and the partition scheme are synchronized at the same time.

Certificates, symmetric keys, and asymmetric keys

SQL Server severely restricts access to certificates, symmetric keys, and asymmetric keys. Consequently, SQL Compare cannot compare all of the properties for a symmetric key.

If certificates, symmetric keys, and asymmetric keys are selected for synchronization, only the permissions are synchronized.

To ignore all certificates, symmetric keys, and asymmetric keys in the synchronization, select the Ignore certificates, symmetric and asymmetric keys project option .

Extended properties on databases

Extended properties on databases that differ are not displayed in the comparison results, but are always synchronized. If you do not want them to be synchronized, select the Ignore extended properties project option.

Numbered stored procedures

SQL Compare does not synchronize numbered stored procedures. However, you can synchronize them by running the synchronization SQL script in your SQL application.

See also

Setting project options

Warnings


Working with other data sources

In this chapter

  • Working with backups
  • Working with snapshots
  • Working with scripts folders


Working with backups

SQL Compare enables you to compare a backup with other data sources. This is useful, for example, when you want to retrieve the schema from a backup and compare it with your database without running a restore operation or copying the backup from a remote network.

If you are comparing two backups, you do not need SQL Server to be installed on your computer.

Note that:

  • Comparing backups is available only in SQL Compare Professional edition.
  • SQL Compare can retrieve the schema from full or differential backups. However, it does not support partial, filegroup, or transaction log backups.
  • When you run a comparison using a backup, SQL Compare locks the backup files when it reads them, and you cannot overwrite, move, or delete them.
  • SQL Compare does not read the log records of backup files, so if the database schema was modified while the backup was being created, it may not be shown as modified in the comparison results.

You can specify a backup as the target; however, note that backups cannot be modified.

Comparing and synchronizing backups

You can:

  • compare a backup with another data source

    For more information, see Setting data sources

  • create a synchronization script from a backup

When you have selected a backup as the target, the synchronization wizard creates a script to update the database from which the backup was created. Backups cannot be modified directly.

When a backup is the source, and a database is the target, the database can be synchronized with the backup.

For more information, see Using the Synchronization wizard

Compatibility with backups

You can compare backups from SQL Server 2008, SQL Server 2005 and SQL Server 2000 databases. SQL Compare supports:

  • native SQL Server backups

    You can use backups created with SQL Server native compression (including row-level, page-level and file-level compression). Note that SQL Server 2008 encrypted backups are not supported.

  • SQL Backup backups

    You can use backups created with SQL Backup version 3 or later; you can use compressed or encrypted backups.

Note that when you set up a comparison that uses a backup, SQL Compare does not support tables that do not have a primary key, unique index, or unique constraint.

See also

Setting data sources

Working with other data sources

Working with snapshots

Working with scripts folders


Working with snapshots

SQL Compare enables you to create a snapshot of a database and compare it with another data source. A SQL Compare snapshot is a binary file containing information about the structure of a database; it does not contain any table data.

You can use snapshots:

  • for simple version control of databases
  • to compare databases on unconnected SQL servers

Creating snapshots

To create a new SQL Compare snapshot, on the File menu, select Create Snapshot. Alternatively, on the Project Configuration dialog box, select a snapshot as one of the data sources, and click click Create Snapshot.

The Create New Snapshot dialog box is displayed:

Under Data source details, specify the details of the source for the snapshot. You can create a snapshot from a database, backup, scripts folder, or another snapshot.

Under Snapshot creation properties, specify a name and location for the snapshot.

SQL Compare automatically detects the case sensitivity of the data source. If you want to override this, clear the Auto detect case sensitivity check box, and select the Treat items as case sensitive check box if required.

By default, SQL Compare decrypts text objects in SQL Server 2008 and SQL Server 2005 databases that were created using the WITH ENCRYPTION option. When comparing large databases, selecting this option can result in slower performance.

Note that:

  • when the source is a snapshot, the case sensitivity and decryption options are not available
  • when the source is a scripts folder, the decryption option is not available
  • you can also create a snapshot using SQL Snapper

    SQL Snapper is a free tool that creates SQL Compare snapshots from SQL Server databases. You can download SQL Snapper from the Red Gate Labs website.

Comparing and synchronizing snapshots

You can:

  • compare a snapshot with another data source

    See Setting data sources

  • create a synchronization script from a snapshot

When a SQL Compare snapshot is the target, synchronization creates a script to update the database from which the snapshot was created. Snapshots cannot be modified directly.

When a snapshot is the source, and a database is the target, the database can be updated to synchronize it with the snapshot.

This is useful, for example, if you want to roll back changes. If you have made changes to a database, and created a snapshot before synchronization, you can then set the snapshot as the source, and the database as the target, to roll back the changes.

For more information, see Using the Synchronization wizard

Compatibility with previous versions of SQL Compare

Snapshots created using SQL Compare versions 3, 4, 5, 6, or 7 can be used in this version of SQL Compare.

However, if the Add WITH ENCRYPTION option was selected when you created a snapshot using SQL Compare version 3, the comparison or synchronization may fail when you use the snapshot in this version of SQL Compare.

See also

Working with scripts folders

Working with backups

Setting data sources

Setting project options

Viewing the comparison results

Understanding the comparison results


Working with scripts folders

A scripts folder is a set of object creation scripts representing a database's schema and (optionally) data. A script file is created for each object, and different object types are stored within subfolders you can specify.

You can use scripts folders:

  • for version control of databases

    For example, you may want to store the script files in a source control system, so that you can track the modified or new objects.

  • to compare databases on unconnected SQL Servers

You can create and compare scripts folders only if you are using SQL Compare Professional Edition.

If you are using a 'check out/edit/check in' (VSS style) source control system, you can use SQL Changeset to integrate SQL Compare Professional Edition with your source control system. For examples and details of how to use SQL Changeset, see Getting started with SQL Changeset

For more information about using scripts folders in source control, see Continuous integration for databases using Red Gate SQL tools (pdf)

Creating a scripts folder

To create a new scripts folder, on the File menu, select Create Scripts Folder.

Alternatively, on the Project Configuration dialog box, select Scripts folder as one of the data sources, then click Create schema scripts:

The Create New Scripts Folder dialog box is displayed:

Under Data source details, specify the details of the source for the scripts folder. You can create a scripts folder from a database, backup, snapshot, or another scripts folder.

Under Scripts folder properties, specify a name and location for the scripts folder.

SQL Compare automatically detects the case sensitivity of the data source. If you want to override this, clear the Auto detect case sensitivity check box, and select the Treat items as case sensitive check box if required.

By default, SQL Compare decrypts text objects in SQL Server 2008 and SQL Server 2005 databases that were created using the WITH ENCRYPTION option. When comparing large databases, selecting this option can result in slower performance.

You can customize the folder structure and character encoding of the scripts folder. To do this, click Scripts creation options. The Edit Scripts Creation Options dialog box is displayed, allowing you to specify the directories in which your database objects and static data are saved.

Note that:

  • when the source is a snapshot, the case sensitivity and decryption options are not available
  • when the source is a scripts folder, the decryption option is not available

Comparing and synchronizing scripts folders

You can:

  • compare a scripts folder with another data source

    See Setting data sources

  • synchronize a scripts folder

When a scripts folder is the target, you can either:

  • Create a synchronization script to update the database from which the scripts folder was created, or
  • Update files in the scripts folder directly

    Note that when an object is dropped during synchronization, its script file is not deleted.

If a scripts folder is the target, and any of the script files that will be modified are designated as read-only, a warning is displayed. If you click Yes to continue, these files will be made writable so that they can be modified. This may occur, for example, when you are working with a source control system that sets files to read-only status in some situations.

For more information, see Using the Synchronization wizard

More information about scripts folders

This section provides further information about using object creation scripts as a data source in SQL Compare.

  • White space

    SQL Server does not always process white space and comments correctly at the beginning and end of the object definition for some objects such as views, stored procedures, and rules. You are therefore recommended to select the Ignore White Space option when you use a scripts folder as a data source.

  • Numbered stored procedures

    Numbered stored procedures are not supported for scripts folders.

  • CLR assemblies

    SQL Compare can compare object creation scripts that contain CLR assemblies, or include paths to assembly files. When synchronizing to a scripts folder, SQL Compare will use the hexadecimal content of the assembly.

  • Certificates, symmetric keys, and asymmetric keys

    These are not supported when you use a scripts folder as a data source.

  • Comments

    When you select a scripts folder as the target data source, SQL Compare preserves comments in object types such as views and stored procedures. However, this is not possible for non-textual object types such as tables. Comments that are part of a table definition will be lost when the table is modified and the object creation script updated.

See also

Setting data sources

Generating a report


The command line interface

In this chapter

  • Getting started with the SQL Compare command line
  • Command line basics
  • Integrating the command line with applications
  • Using the SQL Compare command line
  • Command line syntax


Getting started with the SQL Compare command line

The command line interface provides access to the functionality of SQL Compare. For example, using the command line interface you can:

  • automate the comparison and synchronization of database schema
  • perform scheduled comparisons and synchronizations
  • synchronize multiple databases

You invoke the command line either from a script, such as a batch script or VBScript, or by using the facilities provided by compiled languages such as VB, C++ and C#.

Prerequisites

To use the SQL Compare command line interface, you must have:

  • A SQL Compare Professional Edition, SQL Developer Bundle, or SQL Toolbelt license
  • If you do not have a license, you can use the command line for 14 days.
  • .NET framework version 2.0 or later

    This is required to run the command line interface, but it is not required when you develop applications and scripts that use the command line interface.

  • MDAC 2.8 or later

See also

Command line basics

Integrating the command line with applications

Using the SQL Compare command line

Command line syntax

Simple examples using the SQL Compare command line

Switches used in the command line

Options used in the command line

Exit codes used in the command line


Command line basics

This topic provides information on the following aspects of the SQL Compare command line:

  • Getting help
  • Entering a command
  • Aliases
  • The /Options switch
  • Verbose and quiet switches
  • Redirecting command line output

For details and examples of all of the switches that are available for the SQL Compare command line, see Switches used in the command line

Getting help

To display a brief description of SQL Compare, and basic help on all the command line switches, enter:

sqlcompare /help

For more detailed help enter:

sqlcompare /help /verbose

This displays a detailed description of each switch and the values it can accept (where applicable), and all exit codes. To output the help in HTML format, enter:

sqlcompare /help /verbose /html

Entering a command

When you enter a command, the order of switches is unimportant.

You are recommended to follow the Microsoft convention of separating a switch from its values using a colon as shown below.

/Out:Output.txt

Values that include spaces must be delimited by double quotation marks ( " ). For example:

/Out:"c:\Output_File.txt"

Note that if you delimit a path with double quotation marks, you must not terminate the path with the backslash character ( \ ), because the backslash will be interpreted as an escape character. For example:

Incorrect: /Location:"C:\Packages\"

Correct: /Location:"C:\Packages"

For switches that accept multiple values, use commas to separate the values. For example:

/options:IncludeDependencies,ForceColumnOrder

For switches that accept a compound value, separate each part of the value using a colon.

For example, the /include and /exclude switches are used to include and exclude objects from the comparison and synchronization:

/Include:table:Product

This includes all tables for which the table name contains the word Product.

Aliases

Many of the switches have an alias. The alias provides a convenient short-hand way to specify the switch. For example, /? is the alias for the /help switch, and /v is the alias for the /verbose switch.

Switches and aliases are not case sensitive.

The /Options switch

You can use the /Options switch to change your options. For example, comparisons are not case-sensitive by default; to specify case sensitive comparisons you would use the /Options switch:

/Options:CaseSensitiveObjectDefinition

However, note that if you set any options explicitly, all of the default options are switched off.

For more information about options, and a list of default option settings, see Project configuration options used in the command line.

Verbose and quiet switches

The standard output mode prints basic information about what the tool is doing while it is executing. You can specify verbose and quiet modes using the /verbose and /quiet switches, respectively. In verbose mode, detailed output is printed; in quiet mode, output is printed only if an error occurs.

Redirecting command line output

The command line output can be redirect using either the /Out switch, or the output redirection features provided by the shell in which you are executing the command.

Using /Out

You can use the /out switch to specify the file to which you want output directed:

sqlcompare ... /Out:OutputLog.txt

where OutputLog.txt is the name of the file. If the file exists already, you must also use the /Force switch to force the tool to overwrite the file, otherwise an error will occur.

Other redirection

From the standard command prompt provided by Windows, you can redirect output to a file as follows:

sqlcompare ... > OutputLog.txt

Note that the redirection operator ( > ) and file name must be the last items on the command line.

If the specified file exists already, it will be overwritten. To append output from the tool to an existing file, enter the following:

sqlcompare... >> existinglog.txt

This adds the output to the existing file content, without data being lost.

If you are scripting using a language such as VBScript, JScript, PHP, Perl, or Python, or if you want to access the tool from Web pages using ASP.NET, refer to the documentation for the language.

Specify command line arguments in an XML file that can be referenced using the /Argfile switch. For more on this topic see Using XML to specify command line arguments.

See also

Getting started with the SQL Compare command line

Integrating the command line with applications

Using the SQL Compare command line

Command line syntax

Exit codes used in the command line

Switches used in the command line

Options used in the command line


Integrating the command line with applications

To integrate the SQL Compare command line tool with applications that you distribute to your customers, you must first purchase either a valid SQL Compare Professional Edition, SQL Developer Bundle, or SQL Toolbelt license. When you have purchased the license, and you execute the tool, the distribution files that you need to distribute the applications are generated. These files are marked with an asterisk (*) below.

The files that you should bundle into your application installer are listed below. The files should be installed in the same folder in which your application is installed.

For all SQL Compare functions:

  • SQLCompare.exe
  • RedGate.SQLCompare.Distribution.dll*
  • RedGate.SQLCompare.Distribution.mod*
  • RedGate.Shared.SQL.dll
  • RedGate.Shared.Utils.dll
  • RedGate.SQLCompare.Engine.dll
  • RedGate.SQLCompare.CommandLine.dll
  • RedGate.SQLCompare.ASTParser.dll
  • RedGate.SQLCompare.Rewriter.dll
  • SQLCompare.exe.config
  • RedGate.BackupReader.dll
  • RedGate.BackupReader.sqbReader.dll

For reading backups only:

  • System.Data.SQLite.dll

For reading encrypted backups:

  • RedGate.BackupReader.CryptHelper.dll

For reading compressed backups:

  • zlib1.dll

See also

Getting started with the SQL Compare command line

Command line basics

Using the SQL Compare command line

Command line syntax


Using the SQL Compare command line

In this chapter

  • Simple examples using the SQL Compare command line
  • Example: selecting single tables for comparison
  • Example: selecting tables with unrelated names
  • Using XML to specify command line arguments


Simple examples using the SQL Compare command line

This topic provides some simple examples of how to use the command line interface.

For more detailed information on using the command line, see:

  • Example: selecting single tables for comparison
  • Example: selecting tables with unrelated names
  • Switches used in the SQL Compare command line

Comparing and synchronizing database schema

To compare the database structure of WidgetStaging with WidgetProduction:

sqlcompare /Database1:WidgetStaging /Database2:WidgetProduction

To compare the database structure of WidgetStaging with WidgetProduction, and synchronize the databases by updating WidgetProduction:

sqlcompare /Database1:WidgetStaging /Database2:WidgetProduction
/Synchronize

Using an existing SQL Compare project from the command line

This is useful, for example, as you cannot choose or create a custom filter from the command line, and specifying complex object selections using a regular expression can be unwieldy.

To use a project you have saved as "widgets.scp" from the command line:

sqlcompare /Project:"C:\SQLCompare\Projects\Widgets.scp"

Note that:

  • When you use a project, all objects that were selected for comparison when you saved the project are automatically included.
  • When you use the command line, your project option selections are ignored and the defaults are used. Use /Options to specify any additional options you want to use with a command line project.

    For more information, see Options used in the command line

  • If you want to include or exclude objects from an existing project, you must modify your selection using the graphical user interface.

You cannot use the /Include and /Exclude switches with /Project.

Scheduling or automating a comparison or synchronization

You can use the Microsoft® Windows® Scheduled Task wizard to schedule a comparison by creating a script to run the comparison.

The following example compares the structure of WidgetStaging and WidgetProduction, and outputs the results as the file log_file.txt

First create the script:

cd "C:\Program Files\Red Gate\SQL Compare 8"
sqlcompare /db1:WidgetStaging
/db2:WidgetProduction >> C:\log_file.txt

Next save the script as a .bat file. You specify the .bat file as the program to run from within the Scheduled Task wizard by browsing to it.

To schedule a synchronization of the two databases, updating the database WidgetProduction, you would create the script:

cd "C:\Program Files\Red Gate\SQL Compare 8"
SQLCompare /db1:WidgetStaging /db2:WidgetProduction
/Synchronize

In these examples MS-DOS batch scripting is used, a basic scripting language that is supported on all versions of Windows. If preferred, you could use VBScript, JScript, PHP, Perl, Python or any other scripting language of your choice.

Creating a scripts folder

You can create a folder of object creation scripts representing the schema of a data source. To export WidgetProduction to a scripts folder:

sqlcompare /Database1:WidgetProduction
/Makescripts:"C:\WidgetProductionScripts"

If the folder does not already exist, it is created. All the subfolders containing different object types in the schema are automatically created beneath the specified folder. If the folder does exist, it must be empty or the export will fail.

For more information on scripts folders, see Working with scripts folders

Creating a snapshot

You can create a SQL Compare snapshot representing the schema of a data source. To export WidgetProduction to a snapshot:

sqlcompare /Database1:WidgetProduction
/Makesnapshot:"C:\Snapshots\WP_1.snp"

For more information on SQL Compare schema snapshots, see Working with snapshots

Using a scripts folder or snapshot as a data source

To compare the WidgetProduction scripts folder with the WidgetStaging database:

sqlcompare /Scripts1:"C:\WidgetProductionScripts" /Database2:WidgetStaging

To compare the WidgetStaging database with the WidgetProduction scripts folder and synchronize the scripts

sqlcompare /Database1:WidgetStaging /Scripts2:"C:\WidgetProductionScripts" /Synchronize /Force

The /force switch specifies that any read-only files in the scripts folder that need to be edited during synchronization will be made writable. If you do not include the /force switch and read-only files need to be modified, the synchronization will fail and an error message will be displayed.

To compare two snapshots of WidgetStaging:

sqlcompare /Snapshot1:"C:\Snapshots\WidgetProd_1.snp"
     /Snapshot2:"C:\Snapshots\WidgetProd_2.snp"

To output the synchronization SQL script, for example for auditing purposes, and overwrite the file if it already exists:

sqlcompare /database1:WidgetStaging /database2:WidgetProduction
/scriptfile:"C:\SQLScripts\Widgets.sql" /force

Using a backup as a data source

To compare a backup of WidgetDev with WidgetLive:

sqlcompare /Backup1:"D:\MSSQL\BACKUP\WidgetDev_20080807_143235.sqb"
     /db2:WidgetLive

If you are comparing a backup set that contains multiple files, use the /backupset1 switch to specify the files which make up the first backup set, and use the /backupset2 switches to specify the files which make up the second:

sqlcompare /Backup1:"D:\MSSQL\BACKUP\WidgetDev.bak"
     /Backupset1:"2008-09-23 Full Backup" /db2:WidgetLive

If the backup set switches are not specified, SQL Compare uses the latest backup set.

To specify more than one backup file, separate the file names using semicolons.

sqlcompare /Backup1:"D:\WidgetDev_Full.bak";"D:\WidgetDev_Diff.bak"
     /db2:WidgetDevelopment

For encrypted backups that have been created using SQL Backup, use the /BackupPassword1 and /BackupPassword2 switches to specify the passwords; when there is more than one backup password, separate the passwords using semicolons.

sqlcompare /Backup1:"D:\MSSQL\BACKUP\WidgetDev.sqb" /password1:Pa$$w0rd
     /db2:WidgetLive

Note that if you are comparing a differential backup, you must also specify the associated full backup.

Fore more information, see Working with backups

See also

Example: selecting single tables for comparison

Example: selecting tables with unrelated names

Using XML to specify command line arguments

Options used in the command line

Switches used in the command line

Exit codes used in the command line


Example: selecting single tables for comparison

This shows how you can use the command line, or an XML arguments file to select a single table for comparison.

In this example, the databases contain the following tables (amongst others):

  • Product
  • ProductCategory
  • ProductCostHistory
  • ProductDealerPriceHistory
  • SpecialOfferProduct

You are interested only in the schema differences between the Product tables in two different versions of your database; you are not interested in any of the other tables, or any other objects in the database.

You can replace the database (/db) in this example with either a scripts folder (/scr) or a snapshot (/sn) and the example will still work correctly. For more information, see Examples using the command line

Using the command line

To specify the table to include, you use the /include switch:

sqlcompare /db1:Products1 /db2:Products2 /Include:table
/Include:table:\[Product\] /verbose

where:

/db1:Products1 specifies that you want to compare the database Products1

/db2:Products2 specifies that you want to compare the database Products2

/Include:table specifies that you want to compare only tables; you do not want to compare other objects such as views, stored procedures, and so on. If you omit this argument, SQL Compare compares all tables that have names that match the second /Include switch and all other objects in the databases.

To specify more than one object type for inclusion, use multiple /include switches. For example, to include only tables and views, enter:

/Include:table /Include:view

/Include:table:\[Product\] specifies that you want to compare only the table that has a name that includes the string [Product]

Note that you use .NET standard regular expressions to define the /Include and /Exclude arguments. Therefore, you must escape the square brackets ( [ ] ) with the backslash character ( \ ). Regular expression syntax is beyond the scope of this online help; refer to your Microsoft .NET framework documentation for more information.

You must include the brackets ( [ ] ) in the string; if you specify the argument without the brackets, /Include:table:Product, the ProductCategory table is included because it contains the string Product. The full SQL Server table names are qualified by the owner name in SQL Server 2000, and the schema name in SQL Server 2005/2008, and include brackets. For example (in SQL Server 2000):

[dbo].[Product]

[dbo].[ProductCategory]

and so on. Therefore, the brackets indicate that you are specifying the full table name. To include the owner (or schema) name in the regular expression, you would need also to escape the dot ( . ):

/Include:table:\[dbo\]\ .\[Product\]

The pipe character ( | ) in a regular expression is interpreted as a logical OR. The character must be escaped by the caret character ( ^ ), to prevent the operating system shell from interpreting it as the pipe operator. (Note that if you want to use the caret character itself as part of your regular expression, it must be escaped by a second caret.)

/Verbose specifies that you want to display detailed information about differences between objects

Using XML

You can use XML as follows:

<?xml version="1.0"?>
<commandline>
    <database1>Products1</database1>
    <database2>Products2</database2>
    <verbose/>
    <include>Table</include>
    <include>Table:\[Product\]</include>
</commandline>

To execute the comparison using the XML file, enter the following command:

sqlcompare /Argfile:XMLFileName.xml

where XMLFileName is the name of the XML file.

See also

Simple examples using the SQL Compare command line

Example: selecting tables with unrelated names

Using XML to specify command line arguments

Exit codes used in the command line

Switches used in the command line

Options used in the command line


Example: selecting tables with unrelated names

This example illustrates how you select a number of individual tables for comparison when their names are not related in any way.

In this example, the databases contain the following tables:

  • Product
  • Supplier
  • ProductCategory
  • SpecialOffer
  • Customer
  • Order
  • Invoice

You are interested only in the schema differences between the Product, Customer, Order, and Invoice tables in two different versions of your database, Customers1 and Customers2; you are not interested in any of the other tables, or any other objects in the databases.

Using the command line

To specify the list of tables to include, you use the /include switch. You could use an include switch for each table that you want to compare. However, this could get unwieldy if you have a long list of tables. Instead, you can use the pipe character ( | ) to separate the table names:

sqlcompare /db1:Customers1 /db2:Customers2 /Include:table
     /Include:table:\[Product\]^|Customer^|Order^|Invoice

where:

/db1:Customers1 specifies that you want to compare the database Customers1

/db2:Customers2 specifies that you want to compare the database Customers2

/Include:table specifies that you want to compare only tables; you do not want to compare other objects such as views, stored procedures, and so on. If you omit this argument, SQL Compare compares all tables that match the second /Include switch and all other objects in the databases.

To specify more than one object type for inclusion, use multiple /Include switches. For example, to include only tables and views, enter:

/Include:table /Include:view
/Include:table:\[Product\]^|Customer^|Order^|Invoice

specifies that you want to compare only the tables that have a name that includes the strings [Product], or Customer, or Order, or Invoice

Note that you use .NET standard regular expressions to define the /Include and /Exclude arguments. Therefore, you must escape the square brackets ( [ ] ) with the backslash character ( \ ). Regular expression syntax is beyond the scope of this online help; refer to your Microsoft .NET framework documentation for more information.

You must include the brackets ( [ ] ) in the string; if you specify the argument without the brackets, /Include:table:Product, the ProductCategory table is included because it contains the string Product. The full SQL Server table names are qualified by the owner name in SQL Server 2000, and the schema name in SQL Server 2005/2008, and include brackets. For example (in SQL Server 2000):

[dbo].[Product]

[dbo].[ProductCategory]

and so on. Therefore, the brackets indicate that you are specifying the full table name. To include the owner (or schema) name in the regular expression, you would need also to escape the dot ( . ):

/Include:table:\[dbo\]\ .\[Product\]

The pipe character ( | ) in a regular expression is interpreted as a logical OR. The character must be escaped by the caret character ( ^ ), to prevent the operating system shell from interpreting it as the pipe operator. (Note that if you want to use the caret character itself as part of your regular expression, it must be escaped by a second caret.)

Using XML

You can use XML as follows:

<?xml version="1.0"?>
<commandline>
    <database1>Customers1</database1>
    <database2>Customers2</database2>
    <sync/>
    <include>Table</include>
    <include>Table:\[Product\]|Customer|Order|Invoice</include>
</commandline>

Note that the pipe character ( | ) (and other operating system operators) do not have to be escaped by the caret character ( ^ ) when they are specified in the XML file.

To execute the comparison using the XML file, enter the following command:

sqlcompare /Argfile:XMLFileName.xml

where XMLFileName is the name of the XML file.

See also

Simple examples using the SQL Compare command line

Example: selecting single tables for comparison

Using XML to specify command line arguments

Switches used in the command line

Exit codes used in the command line

Options used in the command line


Using XML to specify command line arguments

You can use an XML file to specify the arguments for the command line interface. You may want to do this because:

  • An XML file is easier to read than a long and complex command line, particularly where complex rules for including and excluding objects are specified.
  • You can easily transform an XML file into other formats using XSLT.

    For example, you could transform your argument file to HTML for presentation on a Web page.

  • Using an XML file overcomes some limitations that can be a problem when you want to specify regular expressions as command line arguments.

    For example, you may want to use the pipe character ( | ) as part of a regular expression, but it causes problems when it is used at the command prompt; if you use an XML file you can use the pipe character with no problems.

  • Most programming languages support XML, through built-in or freely available third-party libraries.

    This makes it easy to generate and process the XML file.

Create the XML file in the following format:

<?xml version="1.0"?> <commandline> <switch_name1/>
<switch_name2>switch_value</switch_name2>
.... </commandline>

For example, for the /Include and /Exclude switches, use the following format:

<include>objecttype:RegularExpression</include>

To execute the command line tools using an XML argument file as input, at the command prompt enter:

sqlcompare /Argfile:XMLfilename.xml

When using an XML file note that:

  • you cannot specify any other switches on the command line except /verbose or /quiet
  • multiple options should be separated with commas:

    <options>n,oc,t</options>

Examples

Below are some examples of XML files that can be used with SQL Compare. The command line versions of the examples (using aliases) are also provided for comparison. To migrate changes in the XML examples, use the <synchronize/> element.

Note that in all these examples, you can use a scripts folder or snapshot in place of a database. Use <scripts1> and <scripts 2> in the XML file for a scripts folder, and <snapshot1> and <snapshot2> for a snapshot. In the command line, replace /db with /scr for a scripts folder or /snp for a snapshot.

To compare all objects in two local databases (Windows authentication):

Using an XML file:

<?xml version="1.0"?>
<commandline>
    <database1>FirstDatabaseName</database1>
    <database2>SecondDatabaseName</database2>
</commandline>

Using the command line:

SQLCompare /db1:FirstDatabaseName /db2:SecondDatabaseName

To compare all objects in databases on different hosts:

Using an XML file:

<?xml version="1.0"?>
<commandline>
    <database1>FirstDatabaseName</database1>
    <server1>Hostname1</server1>
    <database2>SecondDatabaseName</database2>
    <server2>Hostname2</server2>
</commandline>

Using the command line:

sqlcompare /db1:FirstDatabaseName /s1:Hostname1
/db2:SecondDatabaseName /s2:Hostname2

To compare all objects in two databases using SQL Server authentication:

Using an XML file:

<?xml version="1.0"?>
<commandline>
    <database1>FirstDatabaseName</database1>
    <username1>Username1</username1>
    <password1>Password1</password1>
    <database2>SecondDatabaseName</database2>
    <username2>Username2</username2>
    <password2>Password2</password2>
</commandline>

Using the command line:

sqlcompare /db1:FirstDatabaseName /u1:Username1 /p1:Password1
/db2:SecondDatabaseName /u2:Username2 /p2:Password2

To retrieve verbose output of the differences between two databases:

Using an XML file:

<?xml version="1.0"?>
<commandline>
    <database1>FirstDatabaseName</database1>
    <database2>SecondDatabaseName</database2>
    <verbose/>
</commandline>

Using the command line:

sqlcompare /db1:FirstDatabaseName /db2:SecondDatabaseName
           /Verbose

To migrate schema changes from the first database to the second database:

Using an XML file:

<?xml version="1.0"?>
<commandline>
    <database1>FirstDatabaseName</database1>
    <database2>SecondDatabaseName</database2>
    <synchronize/>
</commandline>

Using the command line:

sqlcompare /db1:FirstDatabaseName /db2:SecondDatabaseName
/sync

To compare only tables containing the word "Product":

Using an XML file:

<?xml version="1.0"?>

<commandline>
    <database1>FirstDatabaseName</database1>
    <database2>SecondDatabaseName</database2>
    <include>Table</include>
    <include>Table:Product</include>
</commandline>

Using the command line:

sqlcompare /db1:FirstDatabaseName /db2:SecondDatabaseName
/Include:table /Include:table:\[Product\]

To compare only tables containing the word "Product" except for the "ProductHistory" table:

Using an XML file:

<?xml version="1.0"?>
<commandline>
    <database1>FirstDatabaseName</database1>
    <database2>SecondDatabaseName</database2>
    <include>Table</include>
    <include>Table:Product</include>
    <exclude>Table:ProductHistory</exclude>
</commandline>

Using the command line:

sqlcompare /db1:FirstDatabaseName
           /db2:SecondDatabaseName
           /Include:table /Include:table:\[Product\]
           /Exclude:table:\[ProductHistory\]

Scripts folder database information file

When you create a scripts folder, an XML file containing some basic details about the structure of the database is created. The file is called RedGateDatabaseInfo.xml and is stored in the main scripts folder at the top level.

The file contains the following information, with the defaults as shown:

<?xml version="1.0" encoding="utf-16">
<DatabaseInformation>
     <DefaultCollation>Latin1_General_CI_AS</DefaultCollation>
       <DefaultSchema>dbo</DefaultSchema>
      <DefaultUser>dbo</DefaultUser>
  <DefaultFilegroup>PRIMARY</DefaultFilegroup>
    <DatabaseVersion>9</DatabaseVersion>
</DatabaseInformation>

If you have your own set of SQL creation scripts that you want to use in SQL Compare, you can specify the settings for your schema by editing this file. If the file does not exist when you run the comparison, SQL Compare creates it, using the default values.

See also

Simple examples using the SQL Compare command line

Example: selecting single tables for comparison

Example: selecting tables with unrelated names

Exit codes used in the command line

Switches used in the command line

Options used in the command line


Command line syntax

In this chapter

  • Switches used in the command line
  • Options used in the command line
  • Exit codes used in the command line


Switches used in the command line

This topic provides a list of the switches you can use with the SQL Compare command line.

Note that:

  • the first data source ( /db1, /b1, and so on ) is the source
  • the second data source ( /db2, /b2, and so on ) is the target
  • The command line syntax of previous versions of SQL Compare is considered deprecated, but continues to be supported.

    For example, in SQL Compare 7, the alias for /BackupSet1 was /bs1. In SQL Compare 8, the alias is now /bks1. You can continue to use /bs1 in SQL Compare 8, but a message is displayed informing you of the new alias.

Deprecated command line syntax will cease to be supported at a future release.

/AllowIdenticalDatabases

This switch is deprecated. Instead use /Include:Identical

/Include:Identical suppresses the exit code if the two data sources are identical.

If /Include:Identical is not set, and the data sources are identical, SQL Compare returns the error code 63.

/Argfile:<argfile>

Runs a file containing an XML argument specification:

sqlcompare /Argfile:XMLFileName.xml

For more information see Using XML to specify command line arguments

/Backup1:<filename1>;<filename2>;...;<filenameN>

Alias: /b1

Specifies the backup to be used as the first data source (the source). You must add all of the files making up the backup set you want to compare:

sqlcompare /Backup1:D:\BACKUPS\WidgetStaging.bak /db2:WidgetStaging

To specify more than one backup file, the file names are separated using semicolons:

sqlcompare /Backup1:D:\BACKUPS\WidgetDev_Full.bak;
     D:\BACKUPS\WidgetDev_Diff.bak /db2:WidgetDev

For more information, see Working with backups

/Backup2:<filename1>;<filename2>;...;<filenameN>

Alias: /b2

Specifies the backup to be used as the second data source (the target). You must add all of the files making up the backup set you want to compare:

sqlcompare /db1:WidgetStaging /Backup2:D:\BACKUPS\WidgetStaging.bak

/BackupPasswords1:<Password1>,<Password2>,...,<Password1N>

Alias: /bpsw1

Specifies the password for the first backup:

sqlcompare /Backup1:D:\BACKUPS\WidgetStaging.bak
/BackupPasswords1:P@ssw0rd /db2:WidgetProduction

/BackupPasswords2:<Password1>,<Password2>,...,<Password1N>

Alias: /bpsw2

Specifies the password for the second backup:

sqlcompare /db1:WidgetStaging
/Backup2:D:\BACKUPS\WidgetProduction.bak /BackupPassword2:P@ssw0rd

/BackupSet1:<backupSet>

Alias: /bks1

If you are comparing a backup set that contains multiple files, use the /BackupSet1 switch to specify the files which make up the first backup set, and use the /BackupSet2 switches to specify the files which make up the second:

sqlcompare /Backup1:"D:\MSSQL\BACKUP\WidgetDev.bak"
/BackupSet1:"2008-09-23 Full Backup" /db2:WidgetLive

If the backup set switches are not specified, SQL Compare uses the latest backup set.

To specify more than one backup file, the file names are separated using semicolons.

sqlcompare /Backup1:D:\BACKUPS\WidgetDev_Full.bak;
"D:\BACKUPS\WidgetDev_Diff.bak" /db2:WidgetDevlopment

For encrypted backups that have been created using SQL Backup, use the /BackupPasswords1 and /BackupPasswords2 switches to specify the passwords; when there is more than one password, the passwords are separated using semicolons.

sqlcompare /Backup1:D:\BACKUPS\WidgetDev.sqb /BackupPassword1:Pa$$w0rd
/db2:WidgetLive

/BackupSet2:<backupSet>

Alias: /bks2

Specifies which backup set to use for the second backup:

sqlcompare /db1:WidgetProduction /BackupSet2:"2008-09-23 Full Backup"

/Database1:<database1>

Alias: /db1

Specifies a database to use as the source:

sqlcompare /Database1:WidgetStaging /Database2:WidgetProduction

/Database2:<database2>

Alias: /db2

Specifies a database to use as the target.

/Exclude:<object type>:<regular expression>

Arguments:

  • Additional

only those objects that are not present in the source (eg /db1)
  • Missing

only those objects that are not present in the target (eg /db2)
  • Different

only those objects that are present in both data sources, but are different.
  • Identical

identical objects in the command line output and any generated reports.
  • User specified

objects you specify with a regular expression (eg /Include:Table:WidgetPurchases)

To specify the list of objects to exclude, use the /exclude switch:

sqlcompare /db1:Customers1 /db2:Customers2 /Exclude:table

/Exclude:table specifies that you do not want to compare tables; you only want to compare other objects such as views, stored procedures, and so on.

To specify more than one object or object type type for exclusion use multiple /Exclude switches. For example, to exclude only tables and views:

sqlcompare /db1:WidgetStaging /db2:WidgetProduction
/Exclude:table:WidgetReferences /Exclude:view

Note that you cannot use /Exclude with the /Include and /Project switches.

For a more detailed example using the /include and /exclude switches, see Selecting tables with unrelated names

/Force

Alias: /f

This forces the overwriting of any output files that already exist. If this switch is not used and a file of the same name already exists, the program will exit with the exit code indicating an IO error.

/IgnoreParserErrors

If SQL Compare encounters any high level errors when parsing a scripts folder, it will exit with an error code of 62.

Use /ignoreParserErrors to force SQL Compare to continue without exiting.

/Include:<object type>:<regular expression>

Arguments:

  • Additional

only those objects that are not present in the source (eg /db1)
  • Missing

only those objects that are not present in the target (eg /db2)
  • Different

only those objects that are present in both data sources, but are different.
  • Identical

identical objects in the command line output and any generated reports.
  • User specified

objects you specify with a regular expression (eg /Include:Table:WidgetPurchases)

This switch is used to specify the list of objects to include. You can use an /Include switch for each object that you want to compare. However, this can be unwieldy if there is a long list. Instead, you can use the pipe character ( | ) to separate the table names:

sqlcompare /db1:Customers1 /db2:Customers2 /Include:table
/Include:table:\[Product\]^|Customer^|Order^|Invoice

For more detailed information on using the /Include switch, see: Selecting tables with unrelated names.

/IncludeIdentical:<IncludeIdentical>

This switch is deprecated. Instead use /Include:Identical

/MakeScripts:<folder>

Alias: /mkscr

Creates a scripts folder from the first (source) data source.

sqlcompare /db1:WidgetStaging
/MakeScripts:"C:\Scripts Folders\Widget staging scripts"

If the folder already exists an error will occur. To merge scripts into an existing scripts folder, compare them with that folder and use the /synchronize switch:

sqlcompare /scr1:"C:\Scripts Folders\Widget dev scripts"
/scr2:"C:\Scripts Folders\Widget staging scripts" /Synchronize

For more information, see Working with scripts folders

/MakeSnapshot:<FileName>

Alias: /mksnap

Creates a snapshot from the first (source) data source.

sqlcompare /db1:WidgetStaging
/MakeSnapshot:"C:\Widget Snapshots\StagingSnapshot.snp"

If the file already exists an error will occur, unless you have also used the /force switch.

/Options:<option1>,<option2>,<option3>

Alias: /o

Applies the project configuration options used during comparison or synchronization:

sqlcompare /db1:WidgetStaging /db2:WidgetProduction
/Options:Default,IgnoreWhiteSpace

For a detailed list of these options see: Options used in the command line.

/Out:<FileName>

Redirects console output to the specified file:

sqlcompare /db1:WidgetStaging /db2:WidgetProduction /Out:C:\output file

/OutputProject:<FileName>

Alias: /outpr

Writes the settings used for the comparison to the specified SQL Compare project file:

sqlcompare /db1:WidgetStaging /db2:WidgetProduction
/Options:Default,IgnoreWhiteSpace /OutputProject:"C:\WidgetProject.scp"

This also generates a SQL Compare project file. These files end with a .scp extension. If the file already exists an error will occur, unless you have also used the /force switch.

/OutputWidth:<columns>

Forces the width of console output.

This can be used to ensure that database object names etc are not truncated, and that SQL script lines are not wrapped or broken. This is particularly useful when redirecting output to a file as it allows you to overcome the limitations of the default console width of 80 characters.

/Password1:<password1>

Alias: /p1

The password for the first database (source).

You must also provide a username. If you do not specify a username and password combination, integrated security is used:

sqlcompare /db1:WidgetStaging /UserName1:User1 /Password1:P@ssw0rd
/db2:WidgetProduction /UserName2:User2 /Password2:Pa$$w0rd

Note that this switch is only used if the source is a database. If the source is a backup, use /BackupPasswords1

/Password2:<password2>

Alias: /p2

The password for the second database (target).

/Project

Alias: /pr

Uses a SQL Compare project (.scp) file for the comparison.

To use a project you have saved as "widgets.scp" from the command line:

sqlcompare /Project:"C:\SQLCompare\Projects\Widgets.scp"

Note that:

  • When you use a project, all objects that were selected for comparison when you saved the project are automatically included.
  • When you use the command line, your project option selections are ignored and the defaults are used. Use /Options to specify any additional options you want to use with a command line project.

    For more information, see Options used in the command line

  • If you want to include or exclude objects from an existing project, you must modify your selection using the graphical user interface.

You cannot use the /Include and /Exclude switches with /Project.

The /Project switch is useful, for example, as you cannot specify a custom filter in the command line, and specifying complex object selections using a regular expression can be unwieldy.

For more information on using projects, and what a project contains see Working with projects

/Report:<filename>

Alias: /r

Generates a report and writes it to the specified file.

The type of report is defined by the /ReportType switch. If the file already exists an error will occur, unless you have used the /Force switch:

sqlcompare /db1:WidgetStaging /db2:WidgetProduction
/Report:"C:\reports\WidgetReport.html" /ReportType:Simple

/ReportType:<reporttype>

Alias: /rt

Arguments:

  • XML

Simple XML report
  • Simple

Simple HTML report
  • Interactive

Interactive report
  • Excel

Microsoft Excel spreadsheet

This switch defines the file format of the report produced by the /Report switch. The default setting is XML.

For example:

sqlcompare/db1:WidgetStaging /db2:WidgetProduction
/Report:"C:\reports\WidgetReport.html" /ReportType:Simple

For more information, see Exporting the comparison results

/ScriptEncoding:<scriptencoding>

Alias: /senc

Arguments:

  • UTF8

UTF-8 encoding, without preamble
  • UTF8WithPreamble

UTF-8 encoding, with 3-byte preamble
  • Unicode

UTF-16 encoding
  • ASCII

ASCII encoding

Specifies the character encoding used when writing the SQL script file. The default is UTF8.

For example:

sqlcompare /db1:WidgetStaging /MakeScripts: D:\Scripts Folder
/SyncScriptEncoding:ASCII

/ScriptFile:<scriptfile>

Alias: /sf

Generates a SQL script to migrate the changes which can be executed at a later time. If the file already exists an error will occur, unless you use the /Force switch:

sqlcompare /db1:WidgetStaging /db2:WidgetProduction
/ScriptFile: "C:\Scripts Folder\WidgetSyncScript.sql"

/Scriptfile can be used when the target ( /db2, /scr2, /sn2 ) is a database, a snapshot, or a scripts folder.

If the target is a snapshot or a scripts folder, the generated script modifies a database with the schema represented by that snapshot or scripts folder.

/Scripts1:<folder>

Alias: /scr1

Specifies the script folder to use as the first data source:

sqlcompare /Scripts1:"C:\Scripts Folder\WidgetStagingScript"
/db2:WidgetProduction

/Scripts2:<folder>

Alias: /scr2

Specifies the script folder to use as the second data source.

/Server1:<server1>

Alias: /s1

Specifies the server on which the first (/db1:) data source is located. If an explicit path is not specified, it defaults to Local.

sqlcompare /Server1:Widget_Server\SQL2008 /db1:WidgetStaging
/db2:WidgetProduction

/server2:<server2>

Alias: /s2

This specifies the server on which the second (/db2:) data source is located. If an explicit path is not specified, it defaults to Local.

/snapshot1:<filename>

Alias: /sn1

Specifies the snapshot to use as the first data source:

sqlcompare /Snapshot1:"C:\Snapshots\WidgetStagingSnapshot.snp"
/db2:WidgetProduction

/snapshot2:<filename>

Alias: /sn2

Specifies the snapshot to use as the second data source:

/Synchronize or /Synchronise

Alias: /sync

Synchronizes the databases after comparison.

The target (for example, /db2) is modified; the source (for example, /db2) is not modified:

sqlcompare /db1:WidgetStaging /db2:WidgetProduction
/Synchronize

/UserName1:<username1>

Alias: /u1

The username for the first database.

If no username is specified, integrated security is used.

sqlcompare /db1:WidgetStaging /UserName1:User1 /Password1:P@ssw0rd
/db2:WidgetProduction /UserName2:User2 /Password2:Pa$$w0rd

/UserName2:<username2>

Alias: /u2

The username for the second database.

If no username is specified, integrated security is used.

See also

Options used in the command line

Exit codes used in the command line


Options used in the command line

You can set project configuration options by using the /Options switch.

For example, by default comparisons are not case-sensitive; to specify case-sensitive comparisons use:

/Options:CaseSensitiveObjectDefinition

To specify multiple options, separate the options using commas:

/Options:<option1>,<option2>,<option3>

If you do not explicitly set any options, the defaults are used.

Default options:

  • DecryptPost2KEncryptedObjects
  • IgnoreFillFactor
  • IgnoreWhiteSpace
  • IncludeDependencies
  • IgnoreFileGroups
  • IgnoreUserProperties
  • IgnoreWithElementOrder

If you want to use these defaults with additional options, specify the default argument and the additional options. for example:

/Options:Default,CaseSensitiveObjectDefinition,IgnoreComments

If you do not specify the default argument, only the options you do specify apply.

To specify no options, use the none argument.

Further options are detailed below.

AddWithEncryption

Alias: we

Adds WITH ENCRYPTION when stored procedures, functions, views, and triggers are included in the synchronization.

Note that if you use ADD ENCRYPTION on a SQL Server 2005 database, SQL Compare will not subsequently be able to compare, or synchronize the encrypted objects.

When SQL Compare creates a snapshot, this option is ignored, and WITH ENCRYPTION is not saved in the snapshot.

CaseSensitiveObjectDefinition

Alias: cs

For databases with case-sensitive collation, enables objects with case-sensitive names to be compared and synchronized. For example, considers object names such as ATable and atable as different and performs case-sensitive comparisons on stored procedures, and so on.

You should use this option only if you have databases with binary or case-sensitive sort order.

Note that you should take care when you change this option. For example, if you create a schema snapshot with this option selected and you then compare the snapshot with another database without this option set, SQL Compare may produce unexpected errors.

ConsiderNextFilegroupInPartitionSchemes

Alias: cfgps

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

When this option is selected, if a partition scheme contains a next filegroup, SQL Compare considers the next filegroup in the comparison and synchronization if the partition scheme is extended. The next filegroup does not affect the way in which data is stored

DecryptPost2KEncryptedObjects

Alias: dp2k

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

When this option is selected, SQL Compare decrypts text objects in SQL Server 2005 and SQL Server 2008 databases which were created using the WITH ENCRYPTION option. Note that when comparing large databases with few encrypted objects, selecting this option may result in slower performance.

When this option is not selected, text objects in SQL Server 2005 and SQL Server 2008 databases are shown as different, and cannot be synchronized.

DisableAndReenableDdlTriggers

Alias: drd

This option is used only for SQL Server 2008 and SQL Server 2005 databases..

DDL triggers can cause problems when you run the synchronization. Select this option to disable any enabled DDL triggers before synchronizing the databases, and re-enable those triggers following synchronization.

DoNotOutputCommentHeader

Alias: nc

When this option is set comments and comment headers are not included in the output synchronization script.

ForceColumnOrder

Alias: f

If additional columns are inserted into the middle of a table, this option forces a rebuild of the table so the column order is correct following synchronization. Data will be preserved.

IgnoreBindings

Alias: ib

Ignores bindings on columns and user-defined types when comparing and synchronizing (e.g. sp_bindrule and sp_bindefault clauses would be ignored).

IgnoreCertificatesAndCryptoKeys

Alias: icc

This option is used only for SQL Server 2005 databases.

SQL Server severely restricts access to certificates, symmetric keys, and asymmetric keys. Consequently, SQL Compare cannot compare all of the properties for a symmetric key.

If certificates, symmetric keys, and asymmetric keys are selected for synchronization, only the permissions are synchronized.

IgnoreChecks

Alias: ich

Ignores check constraints when comparing and synchronizing databases.

IgnoreCollations

Alias: ic

Ignores collation orders on character datatype columns when comparing and synchronizing databases.

IgnoreComments

Alias: icm

Ignores comments when comparing views, stored procedures and so on. Comments will still appear in the synchronization scripts.

IgnoreConstraintNames

Alias: icn

Ignores the names of indexes, foreign keys, primary keys, and default, unique, and check constraints when comparing databases. Note that the names will not be ignored when the databases are synchronized.

IgnoreDatabaseAndServerName

Ignores the names of databases and servers when comparing databases.

This option is only used for SQL Server 2008 and SQL Server 2005 databases.

IgnoreExtendedProperties

Alias: ie

Ignores extended properties on objects and databases when comparing and synchronizing databases.

IgnoreFileGroups

Alias: ifg

Ignores filegroup clauses, partition schemes, and partition functions on tables and keys when comparing and synchronizing databases. Partition schemes and partition functions are not displayed in the comparison results.

IgnoreFillFactor

Alias: if

Ignores the fill factor and index padding in indexes and primary keys when comparing and synchronizing databases.

IgnoreFullTextIndexing

Alias: ift

Ignores full-text catalogs and full-text indexes when comparing and synchronizing databases.

IgnoreIdentityProperties

Alias: iip

Ignores the identity property on columns when comparing databases. Note that the identity property will not be ignored when databases are synchronized.

IgnoreIdentitySeedAndIncrement

Alias: isi

For identity properties, ignores only the identity seed and increment values when comparing databases. Note that they will not be ignored when the databases are synchronized.

IgnoreIndexes

Alias: ii

Ignores indexes, statistics, unique constraints, and primary keys when comparing and synchronizing databases.

IgnoreIndexLockProperties

Alias: iilp

Ignores the lock properties of indexes.

IgnoreInsteadOfTriggers

Alias: iit

Ignores INSTEAD OF DML triggers when comparing and synchronizing databases.

IgnoreKeys

Alias: ik

Ignores foreign keys when comparing and synchronizing databases.

IgnoreNotForReplication

Alias: infr

Ignores the NOT FOR REPLICATION option on foreign keys, identities, check constraints and triggers.

If you select this option, the NOT FOR REPLICATION statement will not be displayed in the object creation script for foreign keys, identities, and check constraints.

In the case of triggers, the NOT FOR REPLICATION statement will be displayed in the object creation script, but will be ignored for the purposes of the comparison. When comparing triggers, you should also select the Ignore white space option, but note that this option will also be applied to all objects in the comparison.

Check constraints and foreign keys that contain the NOT FOR REPLICATION statement in their definition will automatically be flagged as WITH NOCHECK. Use the Ignore WITH NOCHECK option to identify these objects as being the same; but note that this will apply to constraints in all objects.

IgnorePermissions

Alias: ip

Ignores permissions on objects when comparing and synchronizing databases.

IgnoreQueueEventNotifications

Alias: iqen

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

Ignores the event notification on queues when comparing and synchronizing databases.

IgnoreQuotedIdentifiersAndAnsiNullSettings

Alias: iq

Ignores SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements. Ignores these common SET statements when comparing views, stored procedures and so on. Note that these statements will not be ignored when the databases are synchronized.

IgnoreReplicationTriggers

Alias: irpt

Ignores replication triggers when comparing and synchronizing databases.

IgnoreStatistics

Alias: ist

Ignores statistics when comparing and synchronizing databases.

IgnoreTriggerOrder

Alias: ito

This option is used for SQL Server 2000 and later databases.

DML triggers can have an order specified, such as FIRST INSERT, LAST UPDATE, and so on. Select this option to ignore the trigger order for DML triggers when comparing and synchronizing databases. Note that the DDL trigger order is not affected.

IgnoreTriggers

Alias: it

Ignores DML triggers when comparing and synchronizing databases.

IgnoreUserProperties

Alias: iup

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

If this option is not selected, SQL Compare compares user properties, such as the type of user (SQL, Windows, certificate-based, asymmetric key based) and any schema. If a user is selected for synchronization, SQL Compare synchronizes the properties where possible.

If you select this option, users' properties are ignored, and only the user name is compared and synchronized.

IgnoreUsers

Alias: iu

When role-based security is used, object permissions are assigned to roles, not users. If this option is selected, SQL Compare compares and synchronizes object permissions only for roles, and members of roles that are roles. Users' permissions and role memberships are ignored.

IgnoreWhiteSpace

Alias: iw

Ignores white space (newlines, tabs, spaces, and so on) when comparing databases. Note that white space will not be ignored when the databases are synchronized.

IgnoreWithElementOrder

Alias: iweo

If a stored procedure, user-defined function, DDL trigger, DML trigger, or view contains multiple WITH elements (such as encryption, schema binding, and so on), select this option to ignore the order of the WITH elements when comparing and synchronizing databases.

IgnoreWithNocheck

Alias: iwn

Ignores the WITH NOCHECK argument on foreign keys and check constraints. Ignores the 'not trusted' flag on foreign keys and check constraints.

Note that foreign keys or constraints that are disabled, are not ignored.

IncludeDependencies

Alias: incd

Includes dependent objects when comparing and synchronizing databases. For example, if a view depends on a table then the table will be synchronized when synchronizing the view.

none

Alias: n

To specify no options, use the none argument.

NoTransactions

Alias: nt

Removes transactions from the synchronization SQL scripts to produce SQL code that is more readable.

If this option is not selected and the synchronization script fails, the script is rolled back to the start of the failed transaction. If this option is selected, the script is not rolled back. This can be useful for detection of errors within a script.

UseClrUdtToStringForClrMigration

Alias: uclr

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

If CLR objects are to be synchronized, this option forces two rebuilds of the table with conversion to and from strings to update the CLR objects, instead of using ALTER ASSEMBLY. For a detailed explanation, see Understanding the synchronization.

This option affects the synchronization only.

See also

Switches used in the command line

Exit codes used in the command line


Exit codes used in the command line

If a task you are performing with the SQL Compare command line interface fails, and you do not see an error message explaining the reason for the failure, you may see one of the exit codes listed below:

0 - Success

3 - Illegal argument duplication

Some arguments must not appear more than once in a command line.

8 - Unsatisfied argument dependency

There is an unsatisfied argument dependency or violated exclusion when the command line is run. For example:

  • /arg2 depends on /arg1 but you have specified /arg2 without specifying /arg1
  • /arg2 cannot be used with /arg1 but you have used both

32 - Value out of range

The numeric value supplied for an argument is outside the range of valid values for that argument.

33 - Value overflow

The value supplied for an argument is too large.

34 - Invalid value

The value supplied for an argument is invalid.

35 - Invalid license

Software license or trial period has expired.

62 - High level parser error

SQL Compare encountered high level errors when parsing a scripts folder.

Use /IgnoreParserErrors to force SQL Compare to continue without exiting.

The Error Parsing Scripts dialog box in the SQL Compare graphical user interface provides additional information to help you resolve script parser errors.

63 - Databases identical

The databases being compared are identical or no objects have been included.

To suppress this error use /Include:Identical

65 - Data error

Data required by SQL Compare is invalid or corrupt.

69 - Resource unavailable

A resource or service required to run SQL Compare is unavailable.

73 - Failed to create report

The report was not created.

74 - I/O error

This is returned if SQL Compare attempts to write to a file that already exists, and the /force switch has not been set.

77 - Insufficient Permission

The action cannot be completed because the user does not have the necessary permission.

See also

Switches used in the command line

Options used in the command line


Working with SQL Changeset

In this chapter

  • Getting started with SQL Changeset
  • Source control systems supported by SQL Changeset
  • Getting the latest database
  • Saving modifications to source control
  • Registering working folders
  • Committing changes
  • Troubleshooting SQL Changeset


Getting started with SQL Changeset

SQL Changeset is a free tool that enables you to integrate your 'check out/edit/check in' (VSS style) source control system with SQL Compare Professional Edition.

When SQL Changeset is running, SQL Compare can retrieve the latest version of a database from your source control system immediately before comparing it with another database, or performing a synchronization, to ensure that you have the most up-to-date information in your comparison results.

Supported programs

SQL Changeset is installed with SQL Compare 6.2 Professional Edition or later.

The following source control systems are supported:

  • Microsoft® Visual SourceSafe® versions 6.0d and 2005
  • Microsoft® Visual Studio® 2005 Team Foundation Server SP1

SQL Changeset may also work with other source control systems, but they are not currently supported.

For more information, see Source control systems supported by SQL Changeset

Using SQL Changeset

SQL Changeset is a notification area program that you can launch from your Start menu, or from within SQL Compare. When SQL Changeset is running, the SQL Changeset icon is displayed in the notification area, at the far right of the taskbar.

You are recommended to register your working folders with SQL Changeset before you start SQL Compare. To find out how to do this, see Registering working folders.

You can use SQL Changeset to view the pending changeset for files contained in your registered working folders. This includes all changes, not only those made by SQL Compare. For details, see Committing Changes.

For examples of how you can use SQL Changeset with SQL Compare, see:

  • Getting the latest database
  • Saving modifications to source

See also

Source control systems supported by SQL Changeset

Getting the latest database

Saving modifications to source control

Registering working folders

Committing changes

Troubleshooting SQL Changeset

Getting started


Getting the latest database

This example demonstrates how to get the latest version of a database that has been stored as a set of script files (a scripts folder) in a source control system. You do this by using SQL Compare with SQL Changeset to synchronize the schema of a local database with the scripts folder.

If you are not familiar with using SQL Compare, you are recommended to follow the worked examples provided in the SQL Compare help before you read this.

In the example, the Super Gizmo Company has a SQL Server database running on a live server. This database contains a number of tables, views, stored procedures, and other database objects. The Super Gizmo Company's development team has been given the task of making a number of changes to the structure of the database. The allocated developer needs to ensure that his local development version of the database is identical to the latest version of the database in source control.

This example uses two databases:

  • GizmoProduction is the production database.

    This database has been exported to a scripts folder, and the scripts folder is stored in the source control system.

  • GizmoDevelopment is the developer's local version of the production database; this is not up-to-date initially.

If you would like to follow the example on your own system, you need to set up two databases; one that has been exported to a scripts folder and stored in your source control system, and one that is live on your SQL Server.

Registering the working folder

The first time you use a particular working folder with SQL Changeset, you must register the working folder with SQL Changeset. Once it has been registered, SQL Changeset remembers it and you can omit this step in future.

  1. From the Start menu, select SQL Changeset.

    If you have not yet registered any working folders, the Register Working Folders dialog box is displayed.

    If you have already registered working folders in SQL Changeset, your source control system may prompt you to enter your logon details (the logon dialog box for your source control system will be displayed; you may need to use Alt+Esc to switch to the dialog box). The Pending Changeset dialog box is then displayed. Close this dialog box, and in the notification area, right-click [image] SQL Changeset, and then click Register Working Folders.

    SQL Changeset displays a tab for each source control system client it detects. Any registered working folders are listed.

  2. Select the tab for the source control system you want to use, and then click New.

    If prompted, enter the authentication details as required by your source control system.

    The source control system displays a dialog box for you to set up the working folder. For example, Microsoft® Visual SourceSafe® displays the Create local project from SourceSafe dialog box.

  3. Specify the working folder, and then click OK.

    In this example, the production database scripts folder is ScriptsFolder\GizmoProduction. The registered working folder is listed in the Register Working Folders dialog box.

  4. Click OK.

    The working folder you selected is registered.

Comparing the scripts folder with the developer's local version

To compare the schema saved as a scripts folder in source control with the developer's database, you set up a comparison project:

  1. In SQL Compare, on the Projects dialog box, click [image] New.

    The Project Configuration dialog box is displayed.

    Set the Source on the left to Scripts folder, and browse to the local working folder that you registered in SQL Changeset.

  2. Select the Get latest version from source control check box.

    This ensures that you will compare the latest version of the production database. The files in the local working folder will be overwritten when you click Compare Now. (Note that SQL Compare will get the latest files again if you re-run this comparison project by clicking Compare Now on the SQL Compare Comparison Projects dialog box. However, if you click Refresh Comparison on the main results page, SQL Compare will not get the latest files.)

    If SQL Changeset is not already running, the Get latest version from source control check box is not available; click the Launch SQL Changeset link to start SQL Changeset.

    The Get latest version from source control check box is also unavailable if SQL Changeset is running, but you have not yet registered the working folder in SQL Changeset; you can open the SQL Changeset Register Working Folders dialog box by clicking the link on the Project Configuration dialog box:

  3. On the right of the Project Configuration dialog box, under Target, set the Data source type to Database, and select the SQL Server and the developer's local database.

    In this example, the GizmoDevelopment database on the local server is the database that will be updated.

  4. Click Compare Now.

    A message dialog box is displayed. If you selected the Close dialog box on completion check box previously, SQL Compare closes this message dialog box automatically.

SQL Compare displays the differences between the latest version of the database schema in source control (GizmoProduction), and the development database (GizmoDevelopment).

Updating the development database

You want to update the development database schema. To do this:

  1. Ensure all the objects are selected for synchronization, and then click [image] Synchronization Wizard.
  2. On the Choose Synchronization Method page, select Synchronize Using SQL Compare.
  3. Click Next to view the Dependencies page (there are no dependencies in this example), and then click Next again to view the Review Script page.

    For more information about these pages, see Using the Synchronization wizard.

  4. Click Synchronize Now to update the development version of the database.

    SQL Compare synchronizes the databases, then re-compares them. A message dialog box shows the progress of the comparison.

  5. Click OK on the progress dialog box, if necessary.

The developer's local database is now identical to the production database in source control, and the developer can proceed with the modifications.

To see how the developer updates the source control version when the modifications have been made, see Saving modifications to source control.

See also

Troubleshooting SQL Changeset

Getting started with SQL Changeset

Saving modifications to source control

Committing changes

Registering working folders


Saving modifications to source control

This example demonstrates how to use SQL Changeset to update a database that has been stored as a set of script files (a scripts folder) in a source control system by synchronizing with the schema of a local version of the database.

Note that the local copy of the scripts folder must be the latest version. If the files in the source control system have been updated since you retrieved them (for example, by another developer), you must merge the differences into the local live database and your local copy of the files before you proceed with the synchronization.

If you are not familiar with using SQL Compare, you are recommended to follow the worked examples provided in the SQL Compare help before you read this.

In the example, the Super Gizmo Company's developer has made a number of changes to the structure of the GizmoProduction database on a local copy of the database. The developer has tested the changes locally on the GizmoStaging database, and now wants to update the source control version of GizmoProduction with the changes.

This example uses two databases:

  • GizmoProduction is the production database

    This database has been exported to a scripts folder. The scripts folder is under source control and registered in SQL Changeset as a working folder.

  • GizmoStaging is the developer's local version of the production database that contains the modifications.

If you would like to follow the example on your own system, you need to set up two databases; one that has been exported to a scripts folder, stored in your source control system, and registered in SQL Changeset, and one that is live on your SQL Server.

Comparing the developer's local version with the scripts folder

To compare the developer's modified database with the latest version of the production database in the source control system, set up a comparison project:

In SQL Compare, on the Projects dialog box, click [image] New.

  1. The Project Configuration dialog box is displayed.
  2. On the left of the Project Configuration dialog box, set the Source to Database, and select the SQL Server and the database.

    In this example, the GizmoStaging database on the local server is selected.

  3. Set the Target on the right to Scripts folder, and browse to the local working folder for the scripts folder.

  4. Clear the Get latest version from source control check box.

    The latest version was retrieved from source control before the modifications were made; see Getting the latest database for details.

  5. Click Compare Now.

    A message dialog box is displayed. If you selected the Close dialog box on completion check box previously, SQL Compare closes this message dialog box automatically.

SQL Compare displays the differences between the local version of the production database and the staging database.

Updating the production database scripts folder

You want to update the scripts folder for the production database. To do this:

  1. Ensure all the objects are selected for synchronization, and then click [image] Synchronization Wizard.
  2. On the Choose Synchronization Method page, ensure that Synchronize sing SQL Compare is selected.
  3. Click Next to view the Dependencies page; there are no dependencies in this example.
  4. Click Next again to view the Review Script page.

    The Files tab lists the files that will be created or modified.

    If any of the files to be modified are not the latest version, SQL Compare displays a warning in the Warnings tab. You must merge the differences into the live development database and your local copy of the files before you proceed with the synchronization.

  5. Click Synchronize Now to update the scripts folder.

    SQL Compare synchronizes the databases, then re-compares them. A message dialog box shows the progress of the comparison.

  6. If necessary click OK on the Progress dialog box.

Committing the changes

SQL Compare has checked out and modified some of the files in the scripts folder, and created some new files. The developer can now check the changes in to the source control system using SQL Changeset. SQL Compare does not need to be running for you to commit the changes.

  1. In the notification area, double-click [image] SQL Changeset, or right-click [image] SQL Changeset and then click Open Pending Changeset.

  2. Select the changes that you want to commit.

    In this example, click [image] All.

  3. In the Comment box, type a comment for these changes.
  4. Click Commit to Source Control.

    The changes are committed, and the list is refreshed. The files are checked in to the source control system. If there are any issues with the check in, for example, if any of the files have been amended in the source control system and require a merge, your source control system will prompt you in the usual way.

  5. Click Close to close the dialog box.

The GizmoProduction scripts folder in the source control system has now been updated with the developer's modifications.

See also

Troubleshooting SQL Changeset

Getting started with SQL Changeset

Getting the latest database

Committing changes

Registering working folders


Registering working folders

To use SQL Changeset to check in and check out files that are under source control, you must register the working folder with SQL Changeset. When you have registered a working folder, SQL Changeset remembers it until you remove the registration.

  1. If SQL Changeset is not already running, from the Start menu, select SQL Changeset.

    If you have not yet registered any working folders, the Register Working Folders dialog box is displayed.

    If you have already registered working folders in SQL Changeset, your source control system may prompt you to enter your logon details. The Pending Changeset dialog box is then displayed. Close this dialog box, and in the notification area, click [image] SQL Changeset, and then click Register Working Folders.

    A tab is displayed for each source control system client detected on your computer. However, note that not all source control systems are supported (see Using SQL Changeset). SQL Changeset displays a warning on the tab if the source control system is not supported.

  2. Select the tab for the source control system, and click New.

    Your source control system may prompt you for authentication details. You should use the same user name for all working folder registrations.

    The source control system that you selected displays a dialog box for you to select the working folder. For example, Microsoft® Visual SourceSafe® displays the Create local project from SourceSafe dialog box.

  3. Specify the working folder, and then click OK.

    The details of the project are displayed in the Register Working Folders dialog box.

    You can add more working folders as required.

  4. Click OK.

    The working folders are registered. If there are any changes pending in a registered working folder, the Pending Changeset dialog box is displayed. For details, see Committing changes.

To remove the registration for a working folder, click the working folder in the list, and then click Remove.

See also

Getting started with SQL Changeset

Committing changes

Getting the latest database

Saving modifications to source control

Troubleshooting SQL Changeset


Committing changes

You can use SQL Changeset to view the pending changeset for files contained in working folders that you have registered with SQL Changeset. This includes all changes, not only those made by SQL Compare.

The color of the SQL Changeset icon in the notification area indicates whether there are changes to be committed:

indicates pending changes

indicates no changes are pending

If you have registered working folders, when you start SQL Changeset you are prompted to log on to your source control system, and then the Pending Changeset dialog box is displayed. If this dialog box is not displayed (for example because you have closed it), double-click the icon, or right-click the icon and then click Open Pending Changeset.

(If you are using Team Foundation Server, the logon dialog box may be displayed behind the Pending Changeset dialog box, and the Pending Changeset dialog box will appear to be waiting for information. Press Alt+Esc to display the logon dialog box.)

You can select a different registered working folder to view from the drop-down list.

To view the local version of the file, right-click the file and then click View Local Version; to see the changes that have been made to a file, right- click the file, and click Show Differences.

Select the check boxes next to the files for which you want to commit changes, type a comment, and then click Commit to Source Control.

To select multiple files, use Ctrl and Shift in the usual way to highlight the files, then right-click and click Select Items.

Note that when you commit the changes, your source control system may display message boxes, such as error messages. These messages may be displayed behind any other windows that you have open, and so may not be visible. Unfortunately, SQL Changeset is unable to access or process these errors. To proceed, you must use Alt+Esc to access the message box, and then clear it, for example by clicking OK. Refer to your source control system documentation for more information on specific errors or messages.

If the changes are committed successfully, [image] is displayed. If there is a problem committing the changes [image] is displayed; move your mouse pointer over the icon to see a tooltip containing the error message from your source control system.

See also

Getting the latest database

Saving modifications to source control

Getting started with SQL Changeset

Registering working folders

Troubleshooting SQL Changeset


Troubleshooting SQL Changeset

This topic provides information about some issues that may arise when you are using SQL Changeset.

Errors

When you use SQL Changeset, you may see errors that are raised by your source control system. Unfortunately, due to the interface provided by the generic source control API, SQL Changeset is unable to access or process these errors. To proceed, you must clear the error message, for example by clicking OK. Refer to your source control system documentation for more information on specific errors or messages.

Note that the error messages are sometimes displayed behind the SQL Changeset (or SQL Compare) windows, which may give the appearance that SQL Changeset is not responding. Use Alt+Esc to access the error message and clear it.

Failed synchronization

If SQL Compare cannot check out all the files when synchronizing, the synchronization process is stopped and a message is displayed. Note that if SQL Compare has already checked out some files before the synchronization is stopped, those files remain checked out; undo the check out for the files in your source control system.

SQL Changeset is not responding

Your source control system may display a message box (such as a an error notification) or an authentication dialog box behind your current windows; this gives the appearance that SQL Changeset is not responding. Unfortunately, due to the interface provided by the generic source control API, SQL Changeset is unable to access or process these message boxes.

To proceed, you must use Alt+Esc to access the message box, and then clear it, for example by clicking OK. Refer to your source control system documentation for more information on specific errors or messages.

Visual Studio 2005 Team Foundation Server not detected

When you display the Register Working Folders dialog box, SQL Changeset automatically detects any SCCI-compliant source control applications installed on your computer. If you have installed Microsoft® Visual Studio® 2005 Team Foundation Server SP1, but SQL Changeset has not detected it, ensure that you have installed the Visual Studio Team Foundation Server MSSCCI Provider.

Deleted files in source control system

If the files in a working folder that you have registered with SQL Changeset are deleted in the source control system by another user, SQL Changeset lists the missing files in the Pending Changeset dialog box with Add as the action on commit. This is because SQL Changeset assumes that the local files are new because they do not exist in the source control system.

If you are using Visual Studio® 2005 Team Foundation Server and you 'get latest', the redundant files are deleted. When you refresh the pending changeset list, the files no longer appear in the list.

If you are using Visual SourceSafe® and you 'get latest', the local files are not deleted. If you do not want to keep the files, you must delete them manually.

Working folders registered for different users

You are advised to register all working folders using only one user name. If you register working folders using different user names, this may cause error messages or failure when you use a working folder that is registered under a user name that is different from the one you are currently using.

If you have already registered working folders using different user names, remove the registrations for the different user names, and re-register the working folders.

Unicode support

Due to limitations of SCCI implementation, SQL Changeset does not support Unicode.

Getting Technical Support

Further support resources such as forums, FAQs, and knowledge bases, are available in our support site.

The support team may ask you to send a log of SQL Changeset's activities; this is located in:

C:\Documents and Settings\<User Name>\Local Settings\Application Data\Red Gate\SQL Changeset\Log <datetime>.txt

In Vista it is located in:

C:\Users\<User Name>\AppData\Local\Red Gate\SQL Changeset\Log <datetime>.txt

See also

Getting the latest database

Saving modifications to source control

Getting started with SQL Changeset

Registering working folders

Committing changes


Acknowledgements

Trademarks and registered trademarks

Red Gate is a registered trademark of Red Gate Software Ltd registered in the U.S. Patent and Trademark Office.

SQL Compare is a registered trademark of Red Gate Software Ltd registered in the U.S. Patent and Trademark Office.

ANTS Profiler, Exception Hunter, SQL Backup, SQL Data Compare, SQL Comparison SDK, SQL Dependency Tracker, SQL Doc, SQL Log Rescue, SQL Multi Script, SQL Packager, SQL Prompt, SQL Refactor, SQL Response, and SQL Toolbelt are trademarks of Red Gate Software Ltd.

Microsoft, Windows, Windows 98, Windows NT, Windows 2000, Windows 2003, Windows XP, Windows Vista, Visual Studio, and other Microsoft products referenced herein are either registered trademarks or trademarks of Microsoft Corporation.

InstallShield is a registered trademark and service mark of InstallShield Software Corporation.

Copyright information

All Red Gate applications are © Red Gate Software Ltd 1999 - 2009

SQL Backup, SQL Compare, SQL Data Compare, SQL Packager, and SQL Prompt contain software that is Copyright © 1995 - 2005 Jean-loup Gailly and Mark Adler.

SQL Doc includes software developed by Aspose (http://www.Aspose.com).

SQL Backup contains software that is Copyright © 2003 - 2008 Terence Parr. Refer to the ACKNOWLEDGEMENTS.txt file in your SQL Backup installation directory for the full license text.


Source control systems supported by SQL Changeset

SQL Changeset supports the following:

  • Microsoft® Visual SourceSafe® versions 6.0d and 2005
  • Microsoft® Visual Studio® 2005 Team Foundation Server SP1

Note that:

  • Microsoft® Visual Studio® 2005 Team Foundation Server SP1 requires the Visual Studio Team Foundation Server MSSCCI Provider

    SQL Changeset my fail to recognize Team Foundation Server if the SCCI Provider is not installed.

  • other versions of Visual SourceSafe or Team Foundation Server can be used with SQL Changeset if they support SCCI. However, any other versions are officially unsupported.

Using unsupported source control systems

SQL Changeset is able to work with any SCCI compliant source control system.

However, SQL Changeset has not been extensively tested with unsupported systems. If you attempt to use an unsupported system, you may encounter errors and unexpected results.

You are therefore recommended not to use unsupported source control systems.

See also

Getting started with SQL Changeset

Getting the latest database

Saving modifications to source control

Registering working folders

Committing changes

Troubleshooting SQL Changeset