SQL Source Control - 3.1

SQL Source Control

Working with migration scripts - SQL Source Control

Migration scripts are customizable change scripts, created in SQL Source Control and used by SQL Compare in deployment.

This article describes:

SQL Compare includes migration scripts in the final deployment script it creates:

What migration scripts contain

Migration scripts can contain overrides to the default deployment script SQL Compare generates, or specific configuration changes for your environment. For example, you may need to disable replication before making certain changes, and re-enable it afterwards. This instruction can be added to the migration script for those changes.

When you deploy a database, SQL Compare checks for migration scripts. If it finds a change that is covered by a migration script, it uses this script instead of the change script it would normally generate.

During deployment with SQL Compare, you can choose which migration scripts to include in deployment, on the Review Migration Scripts page of the Deployment Wizard:

When to use migration scripts

Migration scripts are most useful in two situations:

  • to avoid data loss during deployment
  • to avoid manually making configuration changes with each deployment

Configuration changes will depend on your specific environment and are not discussed in detail here.

The most common situation in which you may need a migration script to avoid data loss are:

  • Adding a NOT NULL constraint to a column

    If you add a NOT NULL constraint to a column in a table that already has data, and the column does not have a default value, deployment will fail. Without a default value, SQL Compare cannot update the table.

    You can create a migration script to specify the default value, and this will be included in deployments.

  • Renaming a table

    When you rename a table in SQL Server Management Studio, SQL Source Control detects this as dropping and re-creating the table. If the table contains data, the data will be lost.

    You can create a migration script to override this by performing the rename with the sp_rename stored procedure.

  • Table and column splits and merges

    Normalization and denormalization activity such as splitting a column can be interpreted by SQL Source Control as dropping one column and creating two. Any data in the column will be lost.

    You can create a migration script to ensure these operations are carried out with ALTER TABLE statements.

  • Changing the data type (or size) of a column

    When you change a column's data type, for example from int to smallint, it is possible to lose data. If the type you are changing to does not accommodate some of the rows, data is truncated during deployment. Similarly, changes to the size of some columns can result in truncation, for example, varchar(50) to varchar(20).

    You can create a migration script to appropriately modify rows that would be truncated.

How to create a migration script

There are four ways to start creating a migration script:

  • From the Object Explorer

    Right-click a database or object, select Other SQL Source Control Tasks, and click Add Migration Script for Specific Changes

  • From the Migrations tab

    Click Add Migration Script

  • From the change list on the Commit Changes tab

    Right-click an object, and click Add New Migration Script

  • From a warning on the Commit Changes tab

    SQL Source Control warns you when you make changes that may have a risk of data loss:

    To add a migration script, click the Create a migration script hyperlink.

Each of way of creating a script launches the Create Migration Script dialog box:

You can create a migration script for the current uncommitted changes, or you can select past changes to include in the script.

When you do so, a new query window opens in SQL Server Management Studio, populated with a default migration script for the changes you selected. Edit this script with the customizations you require, and commit it to source control.

Once the script is committed, it is automatically included in deployment.

See also

Deploying a database from source control

Linking a database to source control

Was this article helpful?

Search support
Forums
Visit the SQL Source Control forum.
Learning SQL Source Control
Knowledge base

SQL Source Control

all SQL products

all products

Notes & articles

SQL Source Control

all SQL products

all products

Demos & walkthroughs

SQL Source Control