Flag to Force a deployment
Occasionally the only changes in a specific build is the addition to the PostScript post-deployment script. Since there are no schema changes, the PostScript change is not executed.
Faking a schema change by adding a comment or something is a poor workaround in an automated CI process.
A simple flag -AlwaysRunUpgrade to always run the PreScript and PostScript as part of the deployment would be ideal.
For clarification for readers, this request is specific to SQL Source Control projects which are deployed by SQL Change Automation. Comments in the item give more detail.
Rutger Benoot commented
Add an option to always execute the Pre/PostScript.sql files when deploying the database and not only when there are changes detected (when used together with SQL Source Control).
This would allow for custom scripts that fix certain thing (e.g.: fix some users/roles which vary by environment).
AdminKendra (Product Manager, Redgate) commented
Apologies for the late follow up on this one, but posting some additional information in case it helps other readers.
This issue is specific to SQL Source Control projects which are deployed with SQL Change Automation.
If you are using a SQL Change Automation project to author your version control changes, it will deploy pre- and post- scripts on every deployment, regardless of whether there is a change to the migrations, or a change to the pre-and post scripts. (This supports scenarios where the database may have drifted and you want to re-apply pre- and post- scripts.)
As alluded to in the suggestion, there is a workaround to force a deployment of a SQL Source Control project, such as adding or updating a lightweight item such as an extended property.
I totally understand that the suggestion is to not need to have that workaround and I see the value in that -- I just wanted to add detail on that for anyone who is curious about what someone might do if they didn't want to edit a procedure, etc.
Thanks for this suggestion and also thanks to those who commented and voted this up.
I fully agree to this idea.
We have run in the issue this week, where expected changes to production did not happen due to this issue.
The post-script is a very usable place to enforce changes to data, for example setting defaults for a new field.
It is a common mistake that the development team first introduces a new column and forgets to set a migration script in the post-script. After a bug report, the issue is fixed in the post-script. If for some reason, there is no other change, the post script is not applied. More so, since we have 3 environments, dev, tst, prd, the redgate tool should be a trusted help, not something to work arround.
Since the postscript should be repeatable by nature, this should not be a problem to apply this every time, given it a much more stable transition to production.