Why is migration script no longer being used for deployment

mjashton

Our Source Control has a single migration script, created to handle the change of data-type to a field in a single table. Initially when using SQL Compare to create deployment scripts this migration script was detected and used, but recently it has stopped being used and so the creation of a deployment script fails and we have to manually intervene and recreate the 'fix' contained within the migration script every time. This obviously isn't great from an automation viewpoint.

I'm not finding any clues as to why the migration script is no longer being used, but did wonder if it was because there are now new dependencies/constraints linked to the altered table which aren't covered by the old migration script? If so, is it possible to change the migration script, or force it to be recreated?

Or maybe I'm barking up the wrong tree and the problem is something else.

Mike U

Migration scripts are intended to run only once against a specific target database, so if you've already run the deployment script on that database then the migration script will no longer be applied. Whether a migration has been run is stored in a table in the database, [RedGateLocal].[DeploymentMetadata].

See https://documentation.red-gate.com/display/SOC5/RedGateLocal.DeploymentMetadata for more details on that table, and Working with migration scripts for general information about how the migrations feature works.

mjashton

A deployment script is created to manage the changes between (a) a snapshot of last-live db and (b) current source-controlled version. We might create this script 3 or 4 times a week on a dev environment, and maybe once a week for a QA environment. At each of these deployments the db for that environment is restored to the last-live db, and the deployment script is run.

I believe in this scenario, whether the migration script has been run or not is reset each time with the restore of the db BAK?

mjashton

A follow up:
I've attempted to edit the migration script (Migrations > Existing migration scripts > view/edit) to include the new dependencies that need dropping/adding before changing the table.
I've added the appropriate changes and clicked the 'save and close' button on the migration script, and it now appears within the 'commit' tab as something new to save to source control. However when I attempt to 'save changes' it Errors with

The migration script Covers changes to: tblCustomer. Script created at 2017-03-30 16:19. could not be found. It may have been deleted.

The script definitely appears in the 'Existing migration scripts' section, but maybe this is pointing to why it is no longer being used when creating a deployment script using SQL Compare? Has something become corrupt, and if so what is the best way to correct this.

Mike U

That's not really the way migration scripts were designed to be used. Migration scripts capture their dependencies when they are first created. Editing the script subsequently does not recalculate those dependencies, so you shouldn't change it such that it modifies different schema objects. The edit feature is really there for things like syntax errors/logic errors in data migrations that mean the script won't actually run.

The intended workflow is that you commit your changes as normal. If you need to make a change that requires a migration because the Compare engine would normally lose data (like your example of changing a data type with an incompatible type), then instead of committing your change, commit a migration script that covers that change (the documentation has an example of renaming a table). You then proceed as normal until the next time you need to make such a change, at which point you add another migration script (not editing the first migration script).

As for what's become corrupted in this case, you should start by looking in the Custom Scripts folder, which is where the migration scripts and their associated metadata is stored, and in particular at the DeploymentOrder.json file which links the scripts together. That's described in the documentation (How migration scripts work). If you can't work out what's wrong, we'd probably need a copy of your scripts folder to diagnose the problem, in which case emailing support@red-gate.com would be the best way forward.