SQL Compare 9

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 synchronization script.
To open the synchronization Wizard, click  Synchronization Wizard.
There are five 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. 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.
  3. Review migration scripts
    View a list of migration scripts you can select to include in the synchronization.
  4. 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.
  5. Review synchronization script
    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 synchronization script will synchronize the database 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 synchronization script will synchronize the database 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 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, synchronization creates a script to modify the data source form which the snapshot or backup was created.

2. 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.

3. Review migration scripts

If SQL Compare detects any migration scripts that apply to the synchronization, the Review migration scripts page of the wizard is displayed:

Migration scripts are customizable change scripts that are committed to source control and re-used in synchronization. For more information, see: Working with migration scripts.

You can select the migration scripts you want to include in the synchronization using the check boxes.

Selecting overlapped migration scripts

By default, SQL Compare selects a migration script with a transition that covers the most versions. You must deselect this default selection before you can select any overlapped migration scripts.

4. 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.

5. Review synchronization script

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: synchronization 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.

 


Didn't find what you were looking for?