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:
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.
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"
Want to Find out More?
Overview
All features
Request a demo
