Generate a migration script
To generate a migration script using dbForge Schema Compare for SQL Server, developers can use the Schema Synchronization wizard that helps them generate a script as follows:
- Save database data, which includes the Data Definition Language (DDL) statements for each object and their dependencies
- Store each database object as a separate SQL file grouped into folders by object type
After the script has been generated, developers can commit it to the version control repository using any third-party version control client. To learn more, refer to How to create a migration script.
For more information about the state-based and migration-based approaches to database version control, see State-based vs migration-based version control.
Cases to use migration scripts
When comparing the actual database with the version in source control, sometimes, it is impossible to distinguish whether the schema changes require any data changes or not. Therefore, deploying data changes from source control can result in deployment failure or data loss.
It is recommended to use migration scripts to ensure correct database deployment. The required steps are to create your deployment script and execute it during automated deployment from source control.
Here are some examples of using migration scripts to deploy the database correctly:
| Examples | Description | Solution |
|---|---|---|
| Adding a NOT NULL constraint to a column | When you insert a NOT NULL constraint into a column holding NULL entries without providing a default value, the deployment process may fail because the new constraint conflicts with the existing NULL values in the column. | Create a migration script to update all the NULL entries of the column with a NOT NULL value and execute it before adding a NOT NULL constraint. |
| Splitting a column | If you split a column into two and drop the original column along with its data, the deployment will cause the dropped column to lose data. | Create a migration script to copy data to the new columns and execute it before dropping the original column. |
| Merging columns | When you merge a column, a new column is created, while the original column is dropped. In this case, the deployment will also lead to data loss of the dropped column. | Create a migration script to transfer data to the new columns and execute it before dropping the original column. |
| Splitting tables | If you split a table into two and drop the original table along with its data, the deployment will cause the dropped table to lose data. | Create a migration script to transfer data to the new table and execute it before dropping the original table. |
| Merging tables | When you merge a table, a new table is created, while the original table is dropped. In this case, the deployment will lead to data loss of the dropped table. | Create a migration script to copy data to the new tables and execute it before dropping the original table. |
| Changing the data type or size of a column | When you change the column data type or size, the data will be truncated during the deployment. | Create a migration script to modify rows in such a manner to avoid truncation. |
| Renaming a table | When you rename a SQL table, it is dropped and then recreated. In this case, the deployment will lead to data loss of the renamed table. | Create a migration script to rename the table using the sp_rename stored procedure. |
Note
The comparison engine of the tools doesn’t handle all cases automatically, so the cases mentioned above must be resolved manually.
Want to Find out More?
Overview
All features
Request a demo
