Allow (once) column drop in database project

Question

I would like to drop one column in existing table. When I simply remove it from table's create script it will cause error (data loss...) on deployment. I would like to allow (in this case) column drop. How you would do that?

Answer 1

To disable the data loss error:

1. Click the Options icon in your schema comparison file.
2. Uncheck "Block on possible data loss".

The setting will change for just that 1 schema comparison and it will be saved within the schema comparison file. If you only want to do this once then you'll need to re-enable the option after you drop the column.

Answer 2

We did this by creating a PreDeployment script to drop the column. Reasoning is we do not want to allow data loss for all objects in the database.

You can create automated version checks to do this only once (see my answer to another post Nontrivial incremental change deployment with Visual Studio database projects for steps how to automate this with SSDT.)

Or you can just supply the script to devops and include instructions in your install manual to run it once for a specific release.

After the release has gone live, you can delete the PreDeployment script.