How to set DriftOptionBlockDataLoss to false in VSTS release plugin

mdbouk

I'm running the latest SQL change automation vsts plugin. I added a new migration script doing the following

ALTER my_table ADD [description] varchar(max) NULL

When the release is been deployed i got the following error

System.Management.Automation.CmdletInvocationException: An unhandled error occurred: RedGate.Versioning.Engine.Api.Exceptions.FileException: Drift analysis: These changes will NOT be applied because DriftOptionBlockDataLoss=True (see 'C:\WINDOWS\TEMP\DLM Automation\5dyfpy0w.ary\artifacts\SQL.Database.Migration_Database_DriftSyncScript.sql' for details). Changes have been detected that could result in data loss for the following table object(s): [dbo].[my_table].

I know how to disable the DriftOptionBlockDataLoss using the MSBuild way but not in the vsts release plugin. 

Also a question here, why adding a null column will raise the data loss error? I'm just adding a null column

Please advice

way0utwest

Apologies for the issues. I haven't seen this, but I haven't done an SCA deploy lately with VSTS. I'll try to give this a test soon and confirm.

way0utwest

OK, I need more information about what you did. I created a new SCA project and deployed it to a database. This worked fine with the SCA build and release tasks (v3) for the table. I then added a new column, as you did, and deployed it. That worked.

Next, I added an index on the target, then added another new column on the dev side and deployed it. This also succeeded, even though there was drift on this table.

Could you have otherwise altered this table (my_table), between deployments? I'm wondering what drift check you're catching. If you run a SQL Compare from the dev db to the prod one (or target), what comes back?

Howardcc

I'm also wondering how to set DriftOptionBlockDataLoss in a VSTS release. (trying to drop a column/table). Any idea how?

MikielAgutu

You can set DriftOptionBlockDataLoss as a property in your .sqlproj file for your SCA project. See https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployment-with-sql-change-automation-core/continuous-integration-msbuild and scroll down to 'Drift report and drift correction script' for more information. The property should then take effect for all deployments of the project, including in VSTS.

mdbouk

Hello, thank you for your reply,

I couldn't test back your solution, what I did is to run MSBuild task to build the SQL Change Automation project.

I will check back the SQL Change Automation Release task later and see what will happen