Using sqlpackage.exe parameters and properties to publish your db with less hassle

2015-09-25

This article is for anyone who already knows how to use sqlpackage.exe. Visit the Microsoft link at the bottom to learn about sqlpackage.exe usage.

Where I work we use sqlpackage.exe to deploy our database snapshots (DACPACs) to various locations.

Since I have started to use this I faced several problems as the script was trying to make changes to the target database.

There were two main problems I faced:

The main reason was that the script would not (by default) ignore data loses. It will normally break and error when it detects a data loss.
The script does not (by default) rollback everything when it aborts the script (e.g. when it detects data losses).
I had real bad situations when the script failed half-way through and left a lot of changes it made till it decided to abort.
I had to restore the db from a backup and go through the whole thing again.

So I found these settings/switches you can use to instruct sqlpackage.exe to get around above issues.

Ignore data losses and continue
/p:BlockOnPossibleDataLoss=False
Specifies whether the publish episode is terminated if the publish operation might cause data loss.

Set this to to ‘true’ and the resulting script will not abort when it detects data losses
/p:IncludeTransactionalScripts=True
Specifies whether to use transactional statements wherever possible when you publish to a database.

Set this flag to true and sqlpackage.exe will do its best to use transactions as much as possible.
This will make sure that you will not end-up with a mess when the script fails.

There are many other useful properties provided by this tool.

for example, you can use /p:GenerateSmartDefaults=True to get sqlpackage.exe to generate default values for new columns that don’t allow nulls.

Read the below link for the full set of options you can use.