ssdt post deployment script run once

Question

I am new to SQL Server Database Tools and may be making incorrect assumptions about what the post deployment scripts are doing.. so correct me if I am wrong.

As far as I am aware the post deployment script will be expected to run after every deployment, not just a single deployment.

If I want to have the post deployment script run a script only one time is there a way to do this without also requiring a version or history table in the database that logs when these scripts were already ran?

I.e. can I have any subsequent amendments to a script be added as a new file within the project with a version number on, and add it to the post deployment script but have the previous script be ignored some how (potentially without first removing it)? regardless of whether it still runs the script during deployment?

Is there a configuration for this sort of thing or is this unintended behaviour?

Rhea

Answer 1

Pre and Post-deploy scripts are designed to be run each time you release the project. Your best practice is to make them repeatable. Add checks so that if the data already exists, you don't run again or something similar. You could build some sort of basic logging table to store this as well - if row not found in the table, run script and put row in table.

You can't tell the project to only run the latest version of a script if there are multiple scripts present because they're all built into one large PreDeploy.sql or PostDeploy.sql file. You'd need something within each section/script that would tell it where to look to know whether or not to run. That could be an existing data check, a table check, version check, or something else, but it would need to know somewhere what to use to know whether or not to run.

There's no way to turn this off other than commenting out the scripts or handling them in some way through SQLCMD variables or other checks. Those would still require you coding in the way each script should run or not. If you give an example or two of what you're trying to accomplish in the Pre/Post Deploy scripts, we can likely give some better guidance.

For our purposes, we make the scripts re-usable and remove them as they've been run. They're still in source control or you can use a snapshot to store that version of the project prior to removing them.

Ladyinwaiting

Your answer is fine that is what I was expecting, I was just wondering if SSDT might have some configuration for "run once" but it can't know the answer to that on its own. – Rhea

I'm not aware of any such configuration. How would the project know that it's been "run once" on any given database without some table or other way to reference the work that's being done? – Ladyinwaiting

I was thinking the project could (should?) know that it ran a script the same way it knows that table adjustments were made. What's the barrier there? – Finned

That's not the way those scripts work. They always run so you need to either find ways to hack around that in your build process or code in some items that don't run the script if it's already been run. Or just make them re-runnable. For longer scripts, either remove when done or write code to not run the script if it's already been run. – Ladyinwaiting