Contents

How to create a migration script

One of the most important requirements for any database is to ensure regular updates that preserve all data. There are two approaches to storing database changes in repositories:

• State-based approach (declarative)
• Migration-based approach (imperative)

dbForge Schema Compare provides the flexibility to use either of these approaches based on the databases you are working with and your professional requirements.

To gain a more comprehensive understanding of the state-based and migration-based approaches in software architecture, refer to the State-based vs. Migration-based blog article.

The guide covers the following topics:

• State-based approach (declarative)
• Migration-based approach (imperative)
• Cases to use migration scripts

State-based approach (declarative)

The state-based approach involves maintaining the database schema in the optimal end state within the code repository. Essentially, when a source database undergoes an update, a new script is produced, enabling the recreation of the database to reflect its state at the time the script was generated.

There are three ways to generate a state-based script:

Scenario 1

1. Select the database under development as Source.

2. Select an empty Scripts Folder as Target.

3. Clear the Compare database properties checkbox on the Options tab of the Schema Comparison Wizard:

4. Click Compare.

5. Once the comparison is over, click Synchronize object to the target database.

6. On the Output tab, select Save the script to a file and click Next.

7. Clear these checkboxes on the Options tab of the Schema Synchronization Wizard:

• Backup path under Database backup
• Use a single transaction under Transactions
• Include USE under Common

8. Click Synchronize.

This way, all of the database objects are consolidated into a single SQL script, which includes the Data Definition Language (DDL) statements for each object, organized based on their dependencies. This makes it possible to create database objects in the correct order when executing the script.

Note

To include table data in a scripts folder, use dbForge Data Compare.

Scenario 2

1. Select the database under development as Source and an empty Scripts Folder as Target.

2. Clear the Compare database properties checkbox on the Options tab of the Schema Comparison Wizard.

3. Click Compare.

4. Once the comparison is over, click Synchronize object to the target database.

5. Select Update the scripts folder on the Output tab and click Next.

6. Click Synchronize.

Each database object is saved as a separate SQL file, organized into folders by object type, similar to a Database Project.

Scenario 3

The third scenario presupposes using dbForge Source Control.

Note

dbForge Source Control only allows working with the state-based approach.

1. Right-click a database in Database Explorer.

2. Point to Source Control and select Link Database to Source Control on the shortcut menu.

3. In the window that opens, select the database under development.

4. Select an empty Scripts Folder as Source control repository.

5. Select the database development model:

• Dedicated - each developer will work on their own copy of a database.
• Shared - all developers will work on a single shared database.

6. Now, if there are any changes made to the database in development, you will be able to click Commit and push the changes directly to the folder that is acting as a repository.

After the changes are committed, dbForge Source Control will generate a structured folder containing SQL files. These files are organized based on the types of objects and comprise SQL files with DDL objects. Subsequently, after implementing modifications to the database and performing a commit operation to the script folder, dbForge Source Control will supersede the prior SQL file containing the DDL object with an updated one.

Migration-based approach (imperative)

In contrast to the state-based approach, the migration-based method operates under a distinct methodology. Instead of relying on a solitary snapshot of an optimal database, it employs a collection of migration scripts that enable the transfer of the existing database from one version to another.

1. Select the database under development as Source.

2. Select a non-empty Scripts Folder as Target. The folder should contain the previously generated scripts, which include the Data Definition Language (DDL) statements for all objects as they existed at the time of generation.

Note

In order to obtain a migration script, it is necessary that at least one element in the Source has been modified in such a way that it differs from the version saved in the Scripts Folder.

3. Click Compare.

4. Once the comparison is over, click Synchronize object to the target database.

5. Select Update the scripts folder on the Output tab and click Next.

6. Click Synchronize.

Once the synchronization is over, you will get a migration script. You can save it as a separate SQL file and commit it to a version control repository.

Note

dbForge Studio for SQL Server supports command-line operations. Hence, you can also create and save the migration script from the command line. For this, provide a path to the desired directory after the /sync argument. For example:

/sync:"C:\sync_script_name.sql"

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. Therefore, deploying data changes from source control can result in deployment failure or data loss.

Migration scripts can be of great assistance in ensuring 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 columns and drop the original column along with its data, the deployment will lead to data loss of the dropped column.	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 a table	If you split a table into two tables and drop the original table along with its data, the deployment will lead to data loss of the dropped table.	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, which is why the cases mentioned above must be resolved manually.

Want to Find out More?

• Previous
• Next