0

I have Two Database some-db-dev & some-db-qa. Both the databases are identical to each other.

I have created a DACPAC File for both the databases. some-db-dev.dacpac and some-db-qa.dacpac respectively.(It had the Table as "A" and Column "Test" in it. It also had some dummy records in it.)

After this I have performed below steps:

  1. Renamed the Table "A" to "ARenamed" from some-db-dev Database.

  2. Generated the DACPAC of "some-db-dev" and Stored it with the name "some-db-dev"

  3. I have fire below command :-

    sqlpackage /a:Script /sf:"C:\Users\some.user\Desktop\some-db-dev.dacpac" /tf:"C:\Users\some.user\Desktop\some-db-qa.dacpac" /tdn:"some-db-qa" /op:"C:\Users\some.user\Desktop\diffscript.sql"

  4. Observations :-

Instead of renaming the Table modified at Step 1. It generated the Script of creating the table as below.

`GO
PRINT N'Creating [dbo].[ARenamed]...';


GO
CREATE TABLE [dbo].[ARenamed] (
    [Id]   NCHAR (10) NULL,
    [Name] NCHAR (10) NULL,
    [Test] NCHAR (10) NULL
);`

Is there something wrong with the command that I am using ??

Any help will be appreciable.

nikita kakraniya
  • 141
  • 2
  • 14
  • Did you perform the renames using SQL Server database project rename refactors? If not, the dacpac will not have knowledge that the intent is a rename rather than a drop/create. – Dan Guzman Jun 19 '19 at 12:20
  • @DanGuzman No, I didn't perform the renaming operation from SSDT Project. I Renamed the table manually from sql server and then report the DACPAC. Table did get renamed in the SQL Server Database. – nikita kakraniya Jun 20 '19 at 04:51
  • Since you didn't make the change using a refactoring operation, SSDT cannot distinguish between a rename and drop/create. It can only compare the source and target schema and doesn't know your intent. – Dan Guzman Jun 21 '19 at 13:48

2 Answers2

1

To rename a table in SSDT you need to use the refactoring tools "right click on the table and do refactor-rename". What happens is this adds an entry to the "RefactorLog.xml" - if you have one of those then when a deployment is created an sp_rename is generated, other you will get what you see here a drop and then create.

See: https://the.agilesql.club/2016/09/refactoring-in-sql-server-data-tools-ssdt/

The last section "Renaming Objects" shows how to do it.

Ed

Ed Elliott
  • 6,666
  • 17
  • 32
0

I think that you'll need to use MSBuild instead. That's an example how to generate script project vs database.

MsBuild.exe "PATH_TO_SQL_PROJ_FILE" ^
  /p:SqlPublishProfilePath="PATH_TO_PUBLISH_PROFILE" ^
  /p:UpdateDatabase=False ^
  /t:Build,Publish
Dmitrij Kultasev
  • 5,447
  • 5
  • 44
  • 88