{"id":87673,"date":"2020-07-27T17:00:37","date_gmt":"2020-07-27T17:00:37","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=87673"},"modified":"2020-07-24T14:18:23","modified_gmt":"2020-07-24T14:18:23","slug":"extracting-dacpac-when-sql-server-refuses","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/extracting-dacpac-when-sql-server-refuses\/","title":{"rendered":"Extracting DACPAC even When SQL Server refuses to"},"content":{"rendered":"

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.<\/p>\n

 <\/p>\n

\"SSMS<\/p>\n

 <\/p>\n

 <\/p>\n

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.<\/p>\n

In order to test this, you can create a broken reference inside a test database using the following script:<\/p>\n

CREATE<\/span>\u00a0TABLE<\/span>\u00a0nonexistingtable<\/span>
\n\u00a0\u00a0(<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0id<\/span>\u00a0\u00a0\u00a0\u00a0INT<\/span>\u00a0IDENTITY<\/span>(<\/span>1<\/span>,<\/span>\u00a01<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0value<\/span>\u00a0INT<\/span>
\n\u00a0\u00a0)<\/span> <\/p>\n

go<\/span> <\/p>\n

CREATE<\/span>\u00a0VIEW<\/span>\u00a0badview<\/span>
\nAS<\/span>
\n\u00a0\u00a0SELECT<\/span>\u00a0id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0value<\/span>
\n\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0nonexistingtable<\/span> <\/p>\n

go<\/span> <\/p>\n

DROP<\/span>\u00a0TABLE<\/span>\u00a0nonexistingtable<\/span>\u00a0 <\/span><\/div>\n

\u00a0<\/p>\n

\"SSMS<\/p>\n

 <\/p>\n

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

 <\/p>\n

Visual Studio<\/strong><\/h3>\n

We can use the SQL Server Object Explorer<\/strong> 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.<\/p>\n

\"VS<\/p>\n

\n\"VS<\/p>\n

 <\/p>\n

Azure Data Studio<\/strong><\/h3>\n

Azure Data Studio has an extension called SQL Server DACPAC<\/strong> which brings to us the Data-Tier Application Wizard to make operations with DACPAC and BACPAC files.<\/p>\n

\"dacpac<\/p>\n

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.<\/p>\n

\"dacpac<\/p>\n

\"dacpac<\/p>\n

 <\/p>\n

Command Line<\/strong><\/h3>\n

You can use the command line. SQLPackage.exe<\/strong> can make a DACPAC extraction from the command line and it has options to avoid the database validation.<\/p>\n

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

The command line would be like this:<\/p>\n

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

We don’t need to include any option to disable the validation because the default behaviour is extracting without it.<\/p>\n

\"sqlpackage\"<\/p>\n

\nConclusion<\/strong><\/h3>\n

\nIn 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<\/p>\n","protected":false},"excerpt":{"rendered":"

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……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6810],"class_list":["post-87673","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87673","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=87673"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87673\/revisions"}],"predecessor-version":[{"id":87690,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87673\/revisions\/87690"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=87673"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=87673"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=87673"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=87673"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}