What are migration scripts?

To deploy changes from version control, the SQL Compare engine generates a deployment script. This is based on the differences between the state in version control and the target database. Migration scripts in version control can define how the SQL Compare engine generates specific sections of this deployment script.

Migration scripts are necessary to avoid data loss when making certain schema changes. To achieve this, the migration script intervenes to make data changes occur at the right point of the deployment.

In most cases, you only need to write SQL for the data changes in the migration script. Schema changes are committed separately and deployed as normal. 

To learn more, see Migration script examples.

Creating a migration script

To create a new migration script:

1. From the Object Explorer, select the database you want to add a migration script to.
2. From the toolbar, select SQL Source Control.
   The SQL Source Control window opens.

3. Go to the Migrations tab.

4. Select the type of migration script, depending on your development process and the changes you're making:

   Start with a blank script

   This creates a blank migration script. Select this option if you've already prepared your schema for the migration and committed those changes.

   Example: splitting a column

   1. Schema change
   Create two new columns

   Commit

   2. Migration script
   Split and copy data to new columns

    Commit

   3. Schema change
   Drop the original column

    Commit

   Use the migration script to make any required data changes, then save and commit the script. If you need to make schema changes, commit them separately.

   Schema changes and migration scripts will be deployed in the order they're committed.

   Replace uncommitted schema changes

   Select this option if you've already made all required schema changes for the migration, and haven't yet committed them.

   SQL Source Control generates a migration script which includes DDL changes for the selected objects. Add data changes to the script, then save and commit.

   Example: splitting a column

   1. Schema change
   Create two new columns

   -

   2. Schema change
   Drop original column

   -

   3. Migration script
   Add data changes

   Commit

   The migration script replaces the section of the deployment script responsible for the selected schema changes.

   Changes to selected objects must occur in the migration script. Adding or removing DDL changes from the generated script will affect the deployment and might cause data loss.

5. In the Name field, enter a name for the script.
6. In the editor window, write SQL to make the required changes.
   Note: Static data changes in migration scripts won't be deployed.
7. Click Save & Close.

8. Commit the changes to version control.
   

   Always commit a new migration script immediately after saving it. Making changes to your database schema between saving and committing migration scripts can cause errors during deployment. 

When you deploy this revision from version control, or use Get latest in SQL Source Control on another machine, the migration script will run as part of the deployment. For more information, see How migration scripts work.

Existing migration scripts

You can edit or delete existing migration scripts from the Migrations tab in SQL Source Control.

To edit or delete an existing migration script:

1. From the Object Explorer, select a database with migration scripts.
2. From the toolbar, select SQL Source Control.
   The SQL Source Control window opens.

3. Go to the Migrations tab.

4. Expand Existing migration scripts.
   Migration scripts on the remote repository are listed. 

5. In the Actions column, click View / Edit next to a migration script.

6. Edit the script to make required changes.

   Guidelines for editing migration scripts

   • Don't create new object dependencies.This is likely to cause errors during deployment.

   • Don't add/remove DDL changes. This might create an invalid state in version control.

   • If you edit the syntax of DDL changes, the resulting schema must stay the same. 

7. Click Save & Close.

8. Go to the Commit tab and commit the updated migration script.

Once committed, the updated migration script is used in all future deployments.

Deploying migration scripts

SQL Compare 11 or later can generate deployment scripts with migrations using the following source/target types:

We recommend using SQL Compare to deploy changes to production, as you have the opportunity to review the deployment script before it's deployed. For a full walkthrough of the deployment process using SQL Compare, see Migration script examples.

It is possible to use the Get latest function in SQL Source Control to deploy these changes, however we don't recommend linking your production database directly to source control.

Dependencies

When you create a migration script that includes uncommitted schema changes, SQL Source Control automatically includes any dependencies. Deselecting any of these dependencies during the deployment stage will cause the deployment to fail.

Static data

Migration scripts do not work with static data. See Static data and migrations.