In addition to automatically Generating migrations scripts based on changes made in the Setting the development source, Redgate Change Control also allows developers to write their own migration scripts independently and import these into the project. This can be useful when developers are provided with a migration script from a third party or don't have direct access to the development source. When migration scripts are added directly to the project, the development source needs to be updated so that it remains in sync with the project. This is achieved in Redgate Change Control by applying pending migration scripts to the development database. This is particularly useful when multiple developers are working on the same project and sharing their migration scripts via version control. Users can pull down the latest version of the project, apply the pending migrations to their local development environment and then continue with their own development.

Applying changes

In your Redgate Change Control window, navigate to the Apply to database page. The table shown on this page is initially filtered to show only pending migration scripts. This can be switched to show all migration scripts in the project. Note: This does not currently show flyway callback scripts.

Any newly created scripts that have been added to the project (in the location specified by the flyway.locations parameter in the flyway.conf) will detected and marked as Pending. If you'd like to make further changes to your script, you can do so in your favourite editor - navigate quickly to the file on disk by clicking the 'Show files on disk' button. Any other unapplied scripts that have been added to your project will also appear here (for example, scripts added by collaborators that have been pulled from version control).

Click the Apply pending button in the top-right corner. This will run the pending scripts against your development database. After the migrations have been applied, a success message will show to indicate the scripts have been applied correctly.

Troubleshooting and repairing

If a migration script fails to run, both the script and the project will be in a Failed state. This is because a failed migration script may have partially executed, putting the database in an unknown state. Should this happen, RCC will display an error message and provide some guidelines on how to repair the problem.

Clicking the Repair... button will show a dialog box with some general instructions for resolving issues. Due to the variety of possible reasons for failure, RCC does not currently offer detailed guidance for the specific failure that occurred.

The fundamental steps are:

1. Fix the cause of the failure. This could be the script itself or the state of the development database (see section below).
2. Revert the development database. The development database needs to be returned to its state prior to running the migration script, if required. In the example shown, the last valid state was after applying V1.3__Drop_LinkedIn_ID.sql.
3. run flyway repair. This can be done from the command line or by clicking the Mark as repaired button in the dialog. See flyway repair documentation for more details about the repair process.

C:\Program Files\Red Gate\Redgate Change Control\flyway>flyway -configFiles="d:\my_rcc_projects\myproject.conf" repair

Fixing the cause of the issue

The migration script could have failed for the following reasons:

1. There is invalid SQL in the script. This may be a result of a typing error.
2. The state of the development database prevents the script from running. This could be because the migration script is trying to create an object that already exists, or is trying to modify an object that has been removed, or a similar mismatch in source and target state expectations.

In the first instance, it's just a question of fixing the errant migration script SQL.

In the second instance, the development database must be altered to be in a state that won't conflict with the running on the migration script.

Reverting the development database

This is necessary only if some statements in the failed migration script have run prior to the failure.

Reverting can be achieved in two ways: manually, or using Schema Compare for Oracle.

Where there are just a few changes, manually undoing the changes could be the simplest option.

If the changes are many and/or complex, using Schema Compare for Oracle can be quicker. To achieve this, perform the following steps:

1. Create a temporary environment using: flyway migrate -version=<n>  (where n is the version prior to the failed migration script running)
2. Load the temporary environment as the source, and your development database as the target and run a comparison. This will enumerate all the differences between the environments.
3. Select all changes and run through the Deployment Wizard.