Working with migration scripts
Published 25 March 2013
This page describes how to use the first version of the migration scripts functionality, released with SQL Source Control 3.0. This version isn't compatible with distributed version control systems (DVCS) such as Git and Mercurial.
We've released a beta version of the V2 migration feature that's compatible with all systems. For more information, including information about how to enable the beta in SQL Source Control, see Migration scripts V2.
Migration scripts are customizable change scripts created in SQL Source Control and used by SQL Compare in deployment.
This page 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 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 columnIf 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.