Instead of renaming a column, the project resets the column and adds the column

Question

I use SSDT to synchronize two databases.

For this, I have a database in Server1, a database in Server2, and a schema in my project.

I always make changes to the Server1 database and apply these changes to the Server2 database. For this, I use this workflow:

• Compare the Server1 database with the Schema and update the Schema with Server1 changes.
• Compare the schema to the Server2 database and update the changes from the schema to the database

This is usually normal, but I found the problem the last time I rename columns in a table.

Usually, if I rename columns in a table, this change appears as a column rename, so when I compare Server1 with Schema, column renames are detected correctly, and I can safely end my workflow.

However, the last time I renamed the columns in the table in Server1, comparing it to Schema, instead of detecting a change as the renaming of the column, it detected this change as a forwarding column (with the old name) and creating a column (with new name). Obviously, if I applied these changes to the Server2 database, I will lose all the data in the renamed column.

Is there a reason for this behavior in SSDT? Can I make SSDT understand that this is a column rename?

I know how to do this manually, but I would prefer to avoid this problem in SSDT or solve it if it appears again in the future.

Answer 1

I think there are some misunderstandings. You have renamed a column in a real database, and now you want to compare the schema with the spread of renaming columns from a database to an SSDT project. It will not work this way because SSDT cannot detect that the column is actually renamed.

The correct scenario is to rename the column to SSDT first (right-click on the column → Refactor → Rename. The refactorlog file will be created - you should not delete it). Then compare the schema between your project and the target database. This change will be propagated to the server when the column is renamed.

Answer 2

Have you used the option refactor -> rename? Here's how to do it, if you did it and it didn’t work, I would have fixed a connection error.

To manually place it or rename it manually, use the refactoring menu, or go to the refactorlog.xml file and it is easy to add it manually.

Let us know what happened / you decided to do it!