Contents

Deploy a database from the source control

When trying to manually build a database using SQL scripts stored in a scripts folder, you might execute the scripts one by one in what appears to be the correct order. However, due to the complex interdependencies between database objects, such as foreign key constraints, it can be challenging to determine the correct sequence. For instance, you might encounter execution errors because a foreign key constraint references a table that hasn’t been created yet, resulting in errors like:

Foreign key ‘FK_foreign_key_name’ references invalid table ‘schema_name.table_name’.

To avoid these issues, consider deploying the database directly from a remote repository using dbForge Schema Compare. This tool eliminates the need for manual execution of scripts step by step and automatically determines the optimal sequence for applying changes by analyzing schema dependencies, including foreign key constraints. By ensuring that all changes are executed in the right order, you minimize the risk of errors and streamline the deployment process.

The guide provides step-by-step instructions on deploying a database from a Git repository.

Prerequisites

Before deploying the database, ensure you have cloned the required repository, for example, using the git clone <url> <path> command or the GUI tool. To clone the repository from the command line, do the following:

1. Create an empty folder on the disk of your computer where the local repository will be stored. In our scenario, the sample folder is development located on disk D.

2. Start Windows Command Prompt (CMD) and navigate to the created empty folder.

3. In the Command Prompt, type the git clone command, paste the URL directory of the remote repository, specify a dot at the end of the command to clone the repository to the folder you have created, and press Enter to create a cloned copy of the repository.

To deploy the database from the source control

1. Open the Schema Comparison Wizard by selecting Schema Comparison Wizard on the Start Page or on the toolbar.

2. On the Source and Target page of the wizard, do the following:

• For previously linked repositories: Select Source Control as a source type, the required repository, and the revision from version control.
• Select Database as a target type, the required connection, and the database to deploy.

Note

If you have not linked the database to the repository, do the following:

On the Source and Target page of the wizard, select Browse.

This will open the Source Control Repository Properties dialog, where you need to specify the following parameters:

• From the Source Control system list, select a source control system.
• In the Repository Folder field, specify a path to the folder with the cloned copy of the repository.
• Optional: The Repository name automatically inherits the name from the repository folder. However, you can specify a different name of the repository.
• Select Test to verify that the database can be successfully connected to source control.
• Select OK to save the changes and close the Source Control Repository Properties dialog.

3. Select Compare to run the comparison.

4. The schema comparison document opens, displaying the database objects. In the grid, select the checkboxes next to the objects you want to deploy and select Synchronize objects to the target database on the top of the grid.

5. On the Output page of the Schema Synchronization Wizard that opens, select the Execute the script directly against the target database option and select Synchronize to deploy the database to the specified revision.

When the synchronization is complete, the schema comparison document will indicate that those objects are identical.

As you can see, dbForge Schema Compare has handled all the tedious tasks of managing SQL script execution. It identified dependencies, automatically arranged the scripts in the correct order, and ensured that the database was deployed without errors or the need for manual intervention. This streamlined process saves significant time and effort and allows you to focus on more critical aspects of database management, while eliminating the risk of executing scripts in the wrong sequence.

Want to Find out More?

• Previous
• Next