Keeping your database updated without losing data is critical to reliable development and deployment. dbForge Studio supports two approaches to version-controlling database changes:
You can choose either approach depending on your database environment and development workflow.
Under the state-based version control approach, the final version of the entire database schema is stored in a code repository. Each database object is saved as a separate SQL file. When developers make changes to the schema, they deploy the updated database locally, generate synchronization scripts using comparison tools, and commit the updated files to version control. This approach ensures that all team members work with the latest version of the schema.
Under the migration-based version control approach, the code repository stores migration scripts that contain DDL statements, such as CREATE, ALTER, and DROP. These scripts update the database schema and data from one version to another. By executing migration scripts, developers can apply changes in a controlled, consistent, and repeatable manner. Each script corresponds to a specific version of the database. As new scripts are added to the repository, the database is updated by running all scripts in sequence.
Note
Although both approaches have advantages and limitations, dbForge Source Control is optimized for the state-based approach. This method makes it easier to compare and synchronize the database schema with the target version, ensuring that all changes are stored accurately and efficiently.
You can generate a migration script using the Schema Synchronization wizard in dbForge Studio for SQL Server. The wizard guides you through the process of generating a script that includes:
After generating the script, you can commit the files to your version control repository using any supported version control client.
For more information about the advantages and limitations of state-based and migration-based database version control, see Database Version Control — State-based or Migration-based.
In some scenarios, the schema comparison engine cannot automatically detect whether changes to the schema require changes to the data. Attempting to deploy such changes directly from source control may lead to data loss or failed deployments.
To avoid these issues, you should create and execute custom migration scripts during automated deployments.
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 columns, a new column is created, and the original column is dropped. This can result in data loss from the dropped column during deployment. | Create a migration script to transfer the data to the new columns and execute it before dropping the original column. |
| Splitting tables | 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 tables, a new table is created, and the original table is dropped. This can result in data loss during deployment. | Create a migration script to copy the data to the new table and run the script before dropping the original table. |
| Changing the data type or size of a column | When you change a column data type or reduce its size, existing data will be truncated during deployment. | Create a migration script to adjust the affected rows in a way that prevents data loss before applying the change. |
| 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 cannot resolve these cases automatically. You must create migration scripts manually to avoid data loss or deployment issues.