Databases in DevOps - Publishing profile

Case
I can’t release my database project due an error “data loss could occur”

error code

Updating database (Failed)
*** Could not deploy package.
Warning SQL72015: The column [dbo].[Table1].[ColumnToRemove] is being dropped, data loss could occur. 

This happens due the fact that my source table changed/removed a column that’s still available in the target table.

Solution
When deploying a database project, you can add an publish file with this deployment. In this file (it’s a xml file) you can set/adjust certain settings
First, we setup visual studio and secondly, we adjust je build pipeline

1) Visual Studio
Here we are going to create the publish file

• Right click on the solution and click “Publish”
• Then a new window pop-up, go to “Advanced”
• Uncheck - 'Block incremental deployment if data loss might occur’ checkbox.

Publish settings - general

• Go to the second tab and check - 'DROP objects in target but not in project' and click “ok”

Publish settings - drop

• Then select “Create profile” and a new a ‘***.publish.xml’ will be added to the solution

Add publish file

• The next step is, is to add the publish file to source control “add ignored file to source control”

Add to source control

2) DevOps 
First, we edit the build pipeline

• Go to Azure DevOps -> Pipelines -> Pipelines
• Then edit your pipeline (please see the blog of Joost for creating a pipeline)
• Go to the task “Copy files” and add to ‘contents’ “**\*.publish.xml” 

Build pipeline

Last step, is to add the xml file to the release pipeline

• Go to pipelines -> release
• Then edit your pipeline and add the publish file

Release pipeline

Summary
In this post you learned how to add a Publish Profile file. In this file you can change the publish settings. In this example we did want to make it possible to truncate tables when a column has been removed.
But it can also be used to disable the deployment of security-related objects to our database, like:

• ExcludeUsers
• ExcludeLogins
• ExcludeDatabaseRoles