In short

Migration scripts are optional scripts you can use to make changes that might otherwise cause data loss or other unwanted effects.

The longer version

Several Redgate tools, including SQL Source Control and SQL Compare, use Redgate's comparison engine to generate deployment scripts. The comparison engine works out the difference between two databases (the source and target) and generates a deployment script to update the target database from one schema to another. This works well for most deployments, and most users won't have to change anything in the deployment script.

However, there are some changes that the comparison engine can't work out correctly. For example, if a table named People in one database is named Customers in another, the comparison engine can't tell they're the same table, even though they contain the same data. Instead, it interprets them as two different tables. The comparison engine sees that the People table only exists in the source database, and the Customers table only exists in the target database, and so generates a comparison script to drop the People table and create a new, empty table named Customers. When the script is run, the data in the People table is lost.

To cover changes like this that the comparison engine can't infer, you can create a migration script containing additional SQL to specify the intent of the change. When you save the migration script, it's added to a table-valued function on the database, and included in the Commit changes tab in SQL Source Control as a change to commit. When the changes are deployed, the comparison engine uses the table-valued functions in the source and target databases to determine which migration scripts need to be run.

Difficult changes can be fixed with a migration script by the developer when they make the change, rather than having to be fixed by a database administrator later. This is especially useful for automated deployment, because database administrators don't have to manually review and edit scripts; migration scripts will cover the changes automatically.

Common examples of when migration scripts are useful

See also: Example V2 migration scripts

Renaming a table

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.

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 doesn't have a default value, the deployment will fail. Without a default value, the comparison engine can't update the table. 

To avoid this, you can create a migration script to specify the default value.

Splitting columns

When you split a column, you create new columns and drop the original column. Any data in the dropped column will be lost. 

To avoid this, you can create a migration script to create the new columns, run custom SQL to move the data to them, and then drop the original column.

Merging columns

When you merge a column, you create a new column and drop the original columns. Any data in the dropped columns will be lost. 

To avoid this, you can create a migration script to create the new column, run custom SQL to move the data to it, and then drop the original columns.

Changing the data type or size of a column

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.