33

I want to extract a database schema of a MSSQLServer database with Server Management Studio. I use the Extract command "Extract Data-tier Application.."

In the database are several references to another database. Because of this I get the following error.

Error extracting database: Validation of the schema model for data package failed. Error SQL71562: Error validating element [dbo].[x] has an unresolved reference to object [dbo].[y]. External references are not supported when creating a package from this platform.

The problem is, that SSMS uses the SQLPackage.exe with parameter /p:VerifyExtraction=True. When I use the console and call SQLPackage.exe without this Parameter, it uses /p:VerifyExtraction=False by default and I can create the .dacpac file.

Is there a way to configure SSMS to disable verification?

Michi-2142
  • 1,170
  • 3
  • 18
  • 39

2 Answers2

56

I wasn't able to find a method that works in SSMS (2008 R2 or 2012), either, but Visual Studio (2013) with SSDT seems to work: Within VS, go to SQL Server Object Explorer, connect to the server in question, right click the database in question, Extract Data-tier Application, and then adjust the Extract Settings, one of which is "Verify extraction". I don't know why MS doesn't just build that into SSMS.

One somewhat-odd thing I noticed from doing, this, though, is that VS will only extract a .DacPac via this method. Even when you choose to add data to the extract, the extension is still .DacPac. I was under the impression that .DacPacs were solely for Schema Only, while .BacPacs were for Schema + Data. Regardless, after VS created the .DacPac (Schema + Data) file, SSMS was able to import it fine using "Deploy Data-tier Application..." wizard.

Jon
  • 578
  • 6
  • 5
34

If you cannot use Visual Studio you can use the command line SqlPackage application to extract the schema from the database. By default, this does not verify the schema (no, I don't know why SSMS and the command line offering have different defaults!). SqlPackage.exe can be found in C:\Program Files (x86)\Microsoft SQL Server\<SQL_VERSION>\DAC\bin.

For example, the following extracts the schema for MyDatabase from the local SQL Server instance and outputs it to a .dacpac file on the local filesystem:

sqlpackage /Action:Extract /SourceDatabaseName:"MyDatabase" /SourceServerName:localhost /TargetFile:"C:\SomeDirectory\MyDatabase.dacpac"

If you want to include schema verification at a later stage, you can set the flag explicitly by adding /p:VerifyExtraction=True to the command line.

Full information on SqlPackage.exe can be found here:

https://msdn.microsoft.com/library/hh550080.aspx

Dave R.
  • 7,206
  • 3
  • 30
  • 52
  • 2
    After chasing my tail for hours with non functioning powershell scripts, inconsistent assemblies and versions (and I admit lack of experience with Powershell), this worked immediately. – Nick.Mc Jun 13 '17 at 03:04
  • @Nick.McDermaid Thanks for the comment, I'm really glad to hear it helped. – Dave R. Jun 14 '17 at 08:33
  • 2
    Appears location for SSMS 17.5 is `C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe` – yvesva Mar 16 '18 at 00:37
  • @yvesva Thanks! Yes, different versions of SQL Server and its tools are stored under separate numbered directories, with `140` corresponding to SQL Server 2017. Instead of hard-coding one of these in my answer, I've now just put `` instead. – Dave R. Mar 19 '18 at 00:40
  • @DaveR. suppose I am trying to extract from Azure SQL Managed Instance. Can I do that with PowerShell also? – CB_Ron Jul 29 '19 at 19:37
  • 1
    @CB_Ron According to the sqlpackage release notes, support for MI was added last year: https://learn.microsoft.com/en-gb/sql/tools/release-notes-sqlpackage?view=sql-server-2017 Both PowerShell and the command line should behave the same. – Dave R. Jul 30 '19 at 10:51
  • @DaveR. Sadly, when using sqlpackage with the `/Action:Export` switch, Verify is not a valid property. So I still cannot export both the schema **and** data. – CB_Ron Aug 01 '19 at 16:29
  • Hi @CB_Ron. Are you sure you're using `VerifyExtraction` and not just `Verify` as the property value?Also, the original question was for schema-only dacpac extraction, I'm afraid. It may be better to author a new question for your particular requirements if you're still having no luck. Sorry my answer didn't help. – Dave R. Aug 02 '19 at 15:29
  • @DaveR. Yes I did use that parameter. It is not valid for `/Action:Export`, verified by the links in your post. I have asked a new question over on dba StackExchange. I'm in the process of testing one of those answers right now. – CB_Ron Aug 02 '19 at 16:34
  • @CB_Ron Use the `Extract` action, not `Export`. You're correct that export doesn't support verification; sorry I didn't spot that when answering you previously. – Dave R. Aug 04 '19 at 17:12