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?
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.
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
