Export a Data-tier Application
Exporting a deployed data-tier application (DAC) or database creates an export file that includes both the definitions of the objects in the database and all of the data contained in the tables. The export file can then be imported to another instance of the Database Engine, or to Windows Azure SQL Database. The export-import operations can be combined to migrate a DAC between instances, to create a logical backup, or to create an on-premise copy of a database deployed in SQL Database.
The export process builds a DAC export file in two stages.
The export builds a DAC definition in the export file – BACPAC file - in the same way a DAC extract builds a DAC definition in a DAC package file. The exported DAC definition includes all of the objects in the current database. If the export process is run against a database that was originally deployed from a DAC, and changes were made directly to the database after deployment, the exported definition matches the object set in the database, not what was defined in the original DAC.
The export bulk copies out the data from all of the tables in the database and incorporates the data into the export file.
The export process sets the DAC version to 220.127.116.11 and the DAC description in the export file to an empty string. If the database was deployed from a DAC, the DAC definition in the export file contains the name given to the original DAC, otherwise the DAC name is set to the database name.
There is a sample application in the SQL Database Labs that can be used to test exporting and importing DACs and databases. For instructions on how to download and use the sample, see Database Import and Export for Windows Azure SQL Database.
Limitations and Restrictions
A DAC or database can only be exported from a database in SQL Database, or SQL Server 2005 Service Pack 4 (SP4) or later.
You cannot export a database that has objects that are not supported in a DAC, or contained users. For more information about the types of objects supported in a DAC, see DAC Support For SQL Server Objects and Versions.
Exporting a DAC requires at least ALTER ANY LOGIN and database scope VIEW DEFINITION permissions, as well as SELECT permissions on sys.sql_expression_dependencies. Exporting a DAC can be done by members of the securityadmin fixed server role who are also members of the database_owner fixed database role in the database from which the DAC is exported. Members of the sysadmin fixed server role or the built-in SQL Server system administrator account named sa can also export a DAC.
To Export a DAC Using a Wizard
Connect to the instance of SQL Server, whether on-premise or in SQL Database.
In Object Explorer, expand the node for the instance from which you want to export the DAC.
Right-click the database name.
Click Tasks and then select Export Data-tier Application…
Complete the wizard dialogs:
This page describes the steps for the Export Data-tier Application Wizard.
Do not show this page again. - Click the check box to stop the Introduction page from being displayed in the future.
Next - Proceeds to the Select DAC Package page.
Cancel – Cancels the operation and closes the Wizard.
Use this page to specify the location where you want the BACPAC file to be created.
Save to local disk - Creates a BACPAC file in a directory on the local computer. Click Browse… to navigate the local computer, or specify the path in the space provided. The path name must include a file name and the .bacpac extension.
Save to Windows Azure - Creates a BACPAC file in a Windows Azure container. You must connect to a Windows Azure container in order to validate this option. Note that this option also requires that you specify a local directory for the temporary file. Note that the temporary file will be created at the specified location and will remain there after the operation completes.
To specify a subset of tables to export, use the Advanced option.
To export a DAC using the Export() method in a .Net Framework application.
To view a code example, download the DAC sample application on Codeplex
Create a SMO Server object and set it to the instance that contains the DAC to be exported.
Open a ServerConnection object and connect to the same instance.
Use the Export method of the Microsoft.SqlServer.Management.Dac.DacStore type to export the DAC. Specify the name of the DAC to be exported, and the path to the folder where the export file is to be placed.