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

This page describes:

• What migration scripts contain
• When to use migration scripts
• How to create a migration script

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 aren't described in detail here.

The most common situations 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 can't 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 make sure 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.

When you create a script, the Create migration script dialog box is shown:

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 this, 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 need, give the script a name, and commit it to source control.

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