Extracting DACPAC even When SQL Server refuses to

There are many situations when you need to extract a DACPAC from an existing database in a SQL Server. These situations are usually related to devOps, we extract the DACPAC to make schema comparisons, generate scripts for schema deployment or deploy directly the DACPAC. It can be used in many different ways during a devOps pipeline.

SSMS Menu

The problem starts when you try to extract a DACPAC using SSMS and it fails. The SSMS extraction makes a validation of the database and if you have any broken reference inside the database – and this happens a lot – SSMS blocks the DACPAC creation.

In order to test this, you can create a broken reference inside a test database using the following script:

CREATE TABLE nonexistingtable
  (
     id    INT IDENTITY(1, 1),
     value INT
  )

CREATE VIEW badview
AS
  SELECT id,
         value
  FROM   nonexistingtable

DROP TABLE nonexistingtable

SSMS Error

Let’s analyze some possible solutions for this problem. We need to generate the DACPAC file even with validation errors on some objects.

Visual Studio

We can use the SQL Server Object Explorer window inside Visual Studio to make the extraction. When we use this window, we have the option to either validate or not the database. Since you know the database has broken links, you ask to not validate and you will have your DACPAC.

VS Menu

VS Option

Azure Data Studio

Azure Data Studio has an extension called SQL Server DACPAC which brings to us the Data-Tier Application Wizard to make operations with DACPAC and BACPAC files.

dacpac extension

There is no option to control the validation on the wizard, but the extraction doesn’t make the validation at all. While SSMS refuses the extraction, it works well on Azure Data Studio.

dacpac wizard

dacpac result

Command Line

You can use the command line. SQLPackage.exe can make a DACPAC extraction from the command line and it has options to avoid the database validation.

The SQLPackage.exe can be found inside SQL Server folders or Visual Studio folders. In my machine, for example, it was located at F:\program files (X86)\Microsoft Visual Studio\2019\enterprise\Common7\ide\extensions\microsoft\sqldb\dac\130

The command line would be like this:

.\sqlpackage /action:Extract /TargetFile:”c:\Bancos\adv.dacpac” /SourceConnectionString:”Server=.;Integrated Security=SSPI;Database=AdventureWorks”

We don’t need to include any option to disable the validation because the default behaviour is extracting without it.

sqlpackage

Conclusion

In the end, SSMS is one of the few tools without the ability to export DACPACs without validation. It’s sad Microsoft is leaving behind a tool which followed DBA’s happy and sad moments since 2005

Register for Simple Talk