13

While trying to update the database in SQL Schema Comparison in Visual Studio, I am getting the below error.

(48,1): SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur. An error occurred while the batch was being executed.

I understand that tool has inspected data loss if it updates.

I was thinking that there would be some option where I ignore this.

After googling I got the below link, but in Visual Studio 2012, https://social.msdn.microsoft.com/Forums/en-US/ce95ac1d-a31c-4e83-904e-78a8491d0761/shema-compare-force-update-with-data-loss?forum=vstsdb

But I don't find any such option in my Schema options

Kgn-web
  • 7,047
  • 24
  • 95
  • 161

3 Answers3

22

In 2015 the sequence is: Create Compare, click on gear icon, general tab, unclick "Block on data loss". I have to set this each time I create a new comparisons, I have been unable to find a way to set a default that sticks other than saving the comparison.

jmoreno
  • 12,752
  • 4
  • 60
  • 91
4

I had this same problem, and unchecking the "Block Incremental Deployment if data loss might occur" didn't fix the issue. I still got lost of errors regarding column size changes that I couldn't work around. I also had to uncheck the "Verify deployment" checkbox, the last item in the lower section, as well.

Robert Sievers
  • 1,277
  • 10
  • 15
4

If deploying the dacpac using sqlpackage.exe command-line utility(used for automating build/deployments like in DevOps), then we need to pass the argument: /p:BlockOnPossibleDataLoss=False

More info here -> https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver15

Meer
  • 678
  • 6
  • 12