HOW Can I Make SSDT Generate Scripts That My DBA Trusts
When you deploy an SSDT project to a database, the overall process looks like:
- Dacpac is compared to the database
- SSDT generates a list of opertations it needs to do to make the database the same as the dacpac
- SSDT has some options like “IgnoreWhitespace” and “IgnoreObjectNameCase”, these are applied to see if the list of operations can be pruned
- SSDT then runs any pre-deploy scripts from the dacpac
- SSDT then runs the code it generated for each operation
- SSDT then runs any post-deploy scripts
- The database is the same as the dacpac
The important thing here which isn’t entirely obvious is that the pre-deploy script is run after the list of things that SSDT needs to do has been generated.
As an example, if you have this table in your dacpac:
create table hello_friend(id int)
and you have a database that only has one table called ‘goodbye_friend’, SSDT will decide it has two operations to do:
- drop the existing unnecessary table
- create the new table
You might actually want to put some of the data from the old table into the new one but if you have a pre-deploy script what do you do? Well you have a couple of options, the first is that you could save the data you need in your pre-deploy script. You could do something like inserting the data into a temp table and then in the post-deploy script copying the data to the final destination after the new table has been created.
This first scenario gets boring pretty quickly, what if your data is 1TB you have now got two writes when one would have been fine.
The second option is to use a pre-pre-create script or a pre-model script.