SQL Source Control 7

As of Nov 1, 2023, if you want to use migration scripts for deployments, we recommend transitioning to Flyway.

Learn more about transitioning from SQL Source Control to Flyway or email us if you have any questions.

Note: You may NOT see the Migrations tab in SQL Source Control, since this was deprecated for all new projects in Jan 2020.

What are migration scripts?

When using a state approach, to deploy changes from version control the SQL Compare engine calculates a deployment script by comparing the state held in version control with the deployment target.

Some schema changes can't be deployed using a state approach. When a deployment involves data changes, it might not deploy successfully, or you could lose data.

Using a migrations deployment approach gives you confidence that these schema changes are deployed correctly. This is especially important when you set have automated your database deployments.

When to use migration scripts

Below are some example changes that require migration scripts to deploy correctly:

If you add a NOT NULL constraint to a column in a table that contains NULL entries, without a default value, the deployment will fail.

Rather than adding a default value, you can write a migration script to update all the existing NULL entries with a NOT NULL value before adding the NOT NULL constraint to the column.

From a schema point of view, when you split a column you actually create two new columns and drop the original one. If you deploy this change, any data in the dropped column will be lost.

To avoid this, you can write a migration script to copy the data to the new columns before dropping the original column. For a walkthrough of this process, see Splitting a column without data loss.

From a schema point of view, when you merge a column, you create a new column and drop the original columns. If you deploy this change, any data in the dropped columns will be lost.

To avoid this, you can write a migration script to copy the data from the original columns to the new column before dropping the original columns.

You can follow the same steps for splitting or merging a tables as you would for splitting or merging columns. To avoid data loss, break the change into multiple commits, and write a migration script to copy the data:

Splitting a table

1	2	3
Create the new table.	Migration script to copy the data to the new table.	Drop the columns in the original table.

Merging tables

1	2	3
Create the new table.	Migration script to copy data from the old tables to the new table.	Drop the original tables.

When you change a column's data type, data might be lost. For example, if the data type you change it to doesn't accommodate some of the rows, data will be truncated during deployment.

To avoid this, you can create a migration script to appropriately modify rows that would otherwise be truncated.

When you rename a table in Management Studio, SQL Source Control interprets this as dropping and recreating the table. If the table contains data, the data will be lost.

To avoid this, you can create a migration script to rename the table with the sp_rename stored procedure.

If you need to insert/update/delete transactional data, then a schema comparison won't work for this. You will need to write your own migration script to manipulate the transactional data.