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?

Superfecundation

Can you try unchecking "Verify Deployment" in publish profile deployment advanced settings and try again? – Remise

@MadBert I tried this, unfortunately same thing happens. – Superfecundation

Another thing I would try is to use Schema Compare tool against an environment you are deploying to. There could be some small change that you might have missed. Let me know if you get to resolve the issue. – Remise

@MadBert I did a schema comparison, no differences, still same error. – Superfecundation

@GiorgosBetsos Does the script contain an actual drop of the table [lut].[KAE]? In Visual Studio 2017, go to Tools > SQL Server > New Schema Comparison.... Click on Options and uncheck Block on possible data loss in General. If you do this you need to manually check the script so nothing gets dropped that you wan't to save. – Pneuma

@Pneuma Yes, the .publish.sql script tries to drop and recreate the table although no changes have been made.If I uncheck the data loss option, then I'll have to check everything manually after a DB dev makes a change. But then there would be no actual benefit in trying to do a publish via VS. – Superfecundation

@GiorgosBetsos Silly idea - could you compare text with Notepad++ and check for invisilbe characters for identifiers? You are using quoting so it may be possible that you have [columnname<some_invisible_character_here>] – Devil

@LukaszSzozda The problem was definitely the refactorlog file. I initially changed the offending table, then reverted it back to its original state. The rafctorlog file somehow incorporated this state transition (back and forth). Upon publish the refactorlog unnecessarily attempted to drop and recreate the table. – Superfecundation

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!

Superfecundation

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?

Pneuma

I've checked SQL Server version, it is the same on both sides. I also did an Update after Schema comparison setting the Visual Studio Database Project as target. Tried then to publish once more. Same error. – Superfecundation

@GiorgosBetsos Can you create a new database from your project to compare with? – Pneuma

I just did so. I noticed that although I was publishing to an empty database, I was getting this message: The following operation was generated from a refactoring log file .... I then noticed that a MyProject.refactorlog file exists (placed on the root level of the project folder). I deleted this file and Voila! Publish worked!!! – Superfecundation

Tricky! Glad everything worked out! If anyone else finds this thread here is some information about the dbo.__RefactorLog and refactorlog file. social.msdn.microsoft.com/Forums/en-US/… :) – Pneuma