SqlPackage.exe Extract data and schema validation

Question

Having installed Sql Server Data Tools, I am attempting to use SqlPackage.exe from: C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\

I'd like to "extract" schema and data. This is so I can migrate to a new Server that does not have any existing database - I want the schema and data to be included in that migration. This is just a spin up / throwaway environment.

The existing databases aren't great in that they have cross database references. The database I want to export has multiple references to another database named "audit". Triggers are involved - hopefully you get the picture.

Having read the docs, I tried this command:

.\sqlpackage.exe /Action:Export /SourceDatabaseName:"MyDb" /SourceServerName:foo\bar /TargetFile:"C:\temp\mydb.bacpac"

However this resulted in many of the following errors:

Error SQL71562: Error validating element [dbo].[foo_u]: Trigge
r: [dbo].[foo_u] has an unresolved reference to object [Audit].[dbo].[Foo].[FooId]. External references are not supported when creating a package from this platform.

I tried specifying /p:VerifyExtraction=false but this resulted in:

.\sqlpackage : *** 'VerifyExtraction' is not a valid argument for the 'Export' action.

So now I am thinking wow this must just not be supported. However in VS2017, when I went to SQL server explorer, I can "Extract data tier application" and choose options for "Extract schema and data" for all tables. This runs fine and produces a "foo.dacpac" file - the UI provides an option for "verify extraction" which I leave unchecked.

Now I am doubly confused because I thought dacpac files contain schema only, and bacpac files were for schema plus data - yet when extracting a data tier application "with data" via visual studio it produces a dacpac file. Does this mean I need to be using the "Extract" action rather than the "Export" action of SqlPackage.exe?

Can anyone offer any guidance on this?

Mikkimiko

Answer 1

This is what I have discovered.

To extract schema plus data, you need to use /Action:Extract NOT /Action:Export.

"Export" produces a bacpac file containing data. This will fall over in case of objects in the source database (triggers, sprocs etc) having external references. Seemingly no way to workaround this.

"Extract" produces a dacpac file, and has the option to also include data, and also you can disable the validation (disabled by default) so you can work around the fact that you have references to external objects.

It seems that Export has been neglected in terms of it's capability, and Extract is more flexible.

This was very confusing.

Mikkimiko

Now, when I'm trying to publish the .dacpac with external references - it fails and seems like there is no parameter to ignore those failures for publishing. Any known workaround for this? – Milesmilesian