Publish of Database Project fails because deployment script attempts to drop and re-create an unmodified table

Question

I'm using Visual Studio 15.8.5 with Sql Server Data Tools 15.1.

I've created an SQL Server database project and imported the schema of an already existing database. I've made several minor changes to a few tables of the database and published the updates to the development database without any problems.

After adding a few SQL scripts to the project, all of them with:

Build Action = None

publish fails, despite no changes have been made in any of the database objects of the project.

This is the part of the auto-generated publish script that causes the problem:

/*
The table [lut].[KAE] is being dropped and re-created since all 
non-computed columns within the table have been redefined.
*/

IF EXISTS (select top 1 1 from [lut].[KAE])
    RAISERROR (N'Rows were detected. The schema update is terminating 
because data loss might occur.', 16, 127) WITH NOWAIT

GO

Table [lut].[KAE] has not been changed, though. One of the scripts is redefining its schema but this should make no difference since this is a 'No Build' script.

What am I possibly doing wrong here?

Edit:

I've done a schema comparison as @MadBert advised. I originally used my actual database as source and my sql server visual studio project as target. No differences were found.

I then switched source and target databases and compared again. The following 'difference' was detected.

As you can see this is not an actual difference, it looks like a Visual Studio bug in schema comparison. Any ideas on how I could circumvent this behavior?

Answer 1

It turned out that a refactor log file was the culprit.

I tried to publish to an empty database, as @Ogglas wisely advised. I noticed that during publish I was getting the following message:

The following operation was generated from a refactoring log file 8e659d92-10bb-4ce9-xxxx-xxxxxxxxx Rename [lut].[KAE].[xxxxx] to $$$$$$$$$ Caution: Changing any part of an object name could break scripts and stored procedures.

I then noticed that my SQL Server Database project contained a .refactorlog file

It seems that this log file was generated after I changed the offending table schema. The schema of the table was later reverted to its original state but the log file remained.

I deleted this log file and after that publish finally succeeded!

Answer 2

Had a similar problem when a SQL Server Database project was set to the wrong Target platform. Edit this in project properties to match the target server. Initiate a schema compare again by right clicking on the project and select Schema Compare....

Also check if Ignore whitespace is marked in Schema Compare Options. If you still have a difference one way or another try pasting the text in Notepad++ with Show All Characters on and see if you can spot a difference.

If you still can't find any difference, try creating a new database from the project and use SSMS GUI to compare. Does the table have the same Lock Escalation settings etc?