Is it possible to extract a DAC without data?

Question

Hi,  I'm using the DacImportExportCli utility, and wondering if it's possible to extract a DAC without data? It doesn't look like it with that tool, but is it possible by using the DAC OM -- DacStore and what not in Microsoft.SqlServer.Management.Dac.

The scenario is that we have an existing Visual Studio database project that we want to be Azure-compatible, so it's been modified to be so but we want that validated in builds, so I'm using that utility to export a .bacpac after local deployment and deploying the .bacpac to Azure to verify compatibility.

Thanks in advance  - Ken

 

Answer 1

Hi Ken,

Thanks for your post.

From what I am understanding, when you extracting a DAC package from either on-premise SQL Server database or SQL Azure database, table data would not extracted to DAC package at all. I usually use the following two ways to deploy a DAC package to SQL Azure:

1. Using SSMS, connect to SQL Azure instance and deploy DAC package via Wizard under Management -> Data-Tier Applications;
2. Using Visual Studio 2010, open Data-Tier Application project in Visual Studio, and deploy to an SQL Azure instance directly.

Please let me know if you have any question.

---

Best Regards,
Chunsong Feng

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

ChungSong is correct.  If you want to move the schema only, you can perform a DAC extraction which will only pull in the schema.  DAC export pulls the schema and the data.

 

You can use the existing tools today in SQL Server 2008 R2 Management Studio to extract a .dacpac from a target database.

 

1. Connect
2. Find database
3. Right click -> tasks -> extract data-tier application
4. walk through wizard

 

Once you have a DACPAC you can install to Azure (or other supported instances) by:

1. Connect
2. Open management node
3. Select "data-tier applications"
4. Right click -> install data-tier application
5. walk through wizard

Answer 3

Hi Ken,

DacImportExportCli doesn't support Extract Schema alone without data.

The following are the supported parameters

-H[elp] | -?             Show this help text.

-X[export]               Perform an export action.

-I[mport]                Perform an import action.

-D[atabase] <database>   Database name to perform the action on.

-F[ile] <filename>       Name of the backup file.

-S[erver] <servername>   SQL Server Name and instance.

-E                       Use Windows authentication, (not valid for SQL Azure)

-U[ser]                  User name for SQL authentication.

-P[assword]              Password for SQL authentication.

-DROP                    Drop a database and remove the DAC registration.(*2)

-EDITION <business|web>  SQL Azure edition to use during database creation.(*4)

-SIZE <1>                SQL Azure database size in GB.(*4)

-N                       Encrypt Connection using SSL.

-T                       Force TrustServerCertificate(*6)

For your information, bacpac created with DacImportExportCLI cannot be extracted from SQL Server Management Studio for 2008 R2, since it support DACPAC not BACPAC

I hope the support for that will be included in the upcoming CTP of SQL Server "Denali"

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, let us know by clicking "Vote as Helpful".

Answer 4

Arunraj.C,

The BACPAC format is an extension of the DACPAC format. The difference between the two formats is limited to the inclusion of data. The DAC Framework included with SQL Server 2008 R2 is missing support for some objects available in SQL Azure today, however, DACFx 2.0 (which is what you are using) has added support for these objects. The EXE you are using is simply exercising DAC's public APIs. The API for extracting a DACPAC is available in DACFx 2.0 - if you are interested you can take a look at the source code for an example of how to exercise DAC public APIs. The code is available here:

http://sqldacexamples.codeplex.com

The upcoming CTP for SQL Server "Denali" will include DACFx 2.0 and using its version of SQL Server Management Studio, you'll be able to extract a DACPAC that stores the schema exactly as how the schema is stored in a BACPAC.  Until then you can either use SSMS for SQL Server 2008 R2 to extract a DAC or call the public APIs available in DACFx 2.0.

 

Answer 5

Thanks Adrian, Any idea on when the Next CTP of Denali will be released ? 

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, let us know by clicking "Vote as Helpful" and Propose as Answer

Answer 6

It will be available this summer.