SSDT and Pre-Model Scripts
pre-deploy scripts in SSDT scripts may be executed before the main diff script is executed, but not before the diff script is generated. This is an important fact to understand. If you want scripts to be executed before the diff is generated, then you need to execute pre-model scripts.
How you go about this is up to you, but there needs to be a few rules that must be followed:
Idempotent: big word, and is used in maths. But don't let that put you off. What it means in this context is that a script can be re-run and the same result happens. So in the context of altering a primary key, the pseudo-code would look something like this:
if database exists then
if table exists then
if primary key name eq "oldname" then
drop primary key then
add primary key with "newname"
and then you add primary key "newname" to the solution. That way the drop and add part of the script will only ever be run once, or again against an older version of the database that has the old primary key. The "newname" of the primary key guarantees that this will be the case.
Stored in the Solution: within database solutions you can add scripts that are part of the solution but are not executed:
If you copy the file to the output, what you'll be able to do is add the files required to your nuspec file and get them deployed as part of the NuGet package. Neat!
So if you have the scripts in the solution and re-runnable then you can be confident that they will run against older versions of the database. It keeps a level of audit and history of the database.
Now, how to run the scripts. As with the create database script, SMO is probably the easiest way to do this. You could use SQLCMD, or Invoke-SqlCmd, but it's entirely up to you.
If you want to use SMO, and you are deploying using Octopus Deploy, then you're in luck: there are scripts available in the Octopus Library to do something like this, but you may will have to alter them to execute a folder full of sql scripts. Fortunately, I already have completed this, this is now available on the Octopus Library. Part two will focus on how to use the step template.