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
