CODEWITHSUNDEEP
devopssql-server-data-toolsdacpac
Gani Lastra
Gani Lastra

Reputation: 255

SSDT- SQL DB DEPLOY Dacpac - on existing production SERVER failed

I am using Azure/Release to SQL Deploy. I have a DACPAC file to deploy, but I (1) have changed column datatypes, (2)deleted some columns on a table that has data already, (3) and added some FK columns. But because there is already production data on this, the deploy fails. What solution should be done on these scenarios? SQL DEPLOY image

  The column [dbo].[TableSample][ColumnSample] is being dropped, data loss could occur. 

  The type for column Description in table [dbo].[Table2] is currently NVARCHAR (1024) NULL but is being changed to NVARCHAR (100) NOT NULL. Data loss could occur. 

  The type for column Id in table [dbo].[Table3] is currently UNIQUEIDENTIFIER NOT NULL but is being changed to INT NOT NULL. 
  There is no implicit or explicit conversion. *** 

  The column [sampleColumnId] on table [dbo].[Table4] must be added
  , but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. 
  To avoid this issue you must either: add a default value to the column, mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.

Upvotes: 2

Views: 2387

Answers (1)

Peter Schott
Peter Schott

Reputation: 4726

Your "data loss" warnings can likely be turned off by using the option to "allow data loss" in your publish options. It's just warning you that you are dropping columns or going to a smaller data length.

Your "Table3" change is just not going to work with keeping the data. GUIDs will not fit in an INT column. You might need to look at dropping/re-creating the table or renaming the current ID column to something else (OldId, maybe) and adding a new ID of type INT, probably with an Identity(1,1).

However the last column - you are trying to add a NOT NULL column with no default to an existing table. Either allow NULLs or put a named default value on the column so the column can be added.

Upvotes: 2

Related Questions