Cannot delete column with only null values when BlockOnPossibleDataLoss=true

Question

I'm using a blue-green deployment strategy with expand contract database pattern. To achieve that on my database deploy schema I've setted the property BlockOnPossibleDataLoss=true because on Expand phase I can modify my database without any break change with the old version.

I had a column that is not necessary anymore so I followed those steps:

1. I've changed this column to allow null values
2. Then my new records don't fill this column anymore
3. I ran a script that setted null for this column to all table records

Now I need to delete this column, but even with all records with NULL value for this column I can't because I got this error:

Rows were detected. The schema update is terminating because data loss might occur.'

How can I delete this column even using BlockOnPossibleDataLoss=true?

Answer 1

1. Create the table with the new schema (without the column you wan't to drop) with a temporary name. Something like tmp_YourTable (Not a temporary table)
2. Insert all data from the source table, to the newly created table
3. Drop the source table
4. Rename the new table, to the old table name. EXEC sp_rename 'tmp_YourTable', 'YourTable';