Stop execution after error and transaction rollback in migration script?
rickya
Posts: 1 New member
Hi,
We are using the Redgate DLM Automation Suite along with Octopus Deploy to deploy changes to our database, using the "Redgate - Deploy from Package 2.0" step.
We encountered a situation, which resulted in data loss during a particular deployment, which consisted of schema changes plus a custom migration script.
The purpose of the deployment was essentially to change the structure of an existing table, by:
1. Creating a new table, MY_NEW_TABLE
2. Inserting data into MY_NEW_TABLE from an existing table, MY_TABLE
3. Dropping MY_TABLE
4. Renaming MY_NEW_TABLE to MY_TABLE
(There are more steps after this, such as updating other schemas, but I have simplified here for demonstration purposes)
During step 2, an error was thrown due to constraints on MY_NEW_TABLE, and since 'XACT_ABORT' is set to ON, the transaction so far was rolled back.
This meant that MY_NEW_TABLE no longer existed. And, instead of the deployment stopping at this point, the process carried on executing the remaining steps, which resulted in the dropping of MY_TABLE.
After investigation, it appears that this is occurring due to the way in which Redgate compiles its script which is executed, including the use of batches (via the GO keyword).
An example of this can be found here: http://brianflove.com/2015/06/02/multiple-batches-in-a-single-transaction/
Since many of the steps after step 2 are auto-generated by Redgate based on an SQL compare with the state of the target database, we do not seem to have control over preventing the rest of the script from executing.
Is there a way to handle this scenario?
Thanks
