This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(265.6, 0%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESG%3C/text%3E%3C/svg%3E)
Hello,
I have 26 tables in my Development database. Using the SQLPackage "Extract" action I am able to generate the DACPAC file and using the "Publish" action deployed in UAT database. All 26 tables deployed successfully in UAT.
Later I deleted 2 tables from Development database and then followed the same steps of extract and publish, but both the tables did not get deleted from UAT database.
I read the Publish action by default will not drop the tables from the target database if not exist is source database. Can anyone guide me how we can achieve this?
Thanks,
Sachin
Please use the SqlPackage parameter named DropObjectsNotInSource. Please read documentation here. It is a boolean parameter.
You may want to consider also having BlockOnPossibleDataLoss parameter disabled.
@Alberto Morillo ... this worked and I was going through all the other arguments from https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver15. I was planning to take the UAT and Production Database backup before deploying the changes from Dev database. To do that I used the BackupDatabaseBeforeChanges=(BOOLEAN) argument but it gives me below error.
*** Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 40515, Level 15, State 1, Line 16 Reference to database and/or server name in 'master.dbo.xp_instance_regread' is not supported in this version of SQL Server.
Error SQL72045: Script execution error. The executed script:
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
DECLARE @rc AS INT, @fn AS NVARCHAR (4000), @ctor AS NVARCHAR (4000);
EXECUTE @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @ctor OUTPUT, 'no_output';
IF (@rc = 0)
SELECT @ctor = @ctor + N'';
IF (@ctor IS NULL)
BEGIN
@rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @ctor OUTPUT, 'no_output';
IF (@rc = 0)
SELECT @ctor = @ctor + N'';
END
On Pltaform as a Service (PaaS) you cannot hace access to the operating system. that is the reason that xp_instance_regread procedure is not available on Azure SQL but is available on SQL Server.
On Azure SQL consider to export the database as a way to backup it, and consider the Import as a restore operation
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 52%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELM%3C/text%3E%3C/svg%3E)
Hi @sachin gupta
You could specify property DropObjectsNotInSource=(BOOLEAN) to the Publish action.
Specifies whether objects that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.
You can find more information about this utility and SQLPackage actions from the official documentation from Microsoft.
Best regards,
LiHong