Export a Data-tier Application

Exporting a deployed data-tier application (DAC) or database creates an export file that includes the definitions of the objects in the database and all of the data in the tables. The export file can then be imported to another instance of the Database Engine, or to Azure SQL Database. The export-import operations can be combined to migrate a DAC between instances, to create an archive, or to create an on-premises copy of a database deployed in SQL Database.

Prerequisites

The export process builds a DAC export file in two stages.

1. The export builds a DAC definition in the export file - BACPAC file - 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. Suppose the export process is run against a database initially deployed from a DAC, and changes were made directly to the database after deployment. In that case, the exported definition matches the object set in the database, not what was defined in the original DAC.
2. 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 1.0.0.0 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.

Permissions

To export a DAC, you need to possess at least ALTER ANY sign-in and database-level VIEW DEFINITION permissions and SELECT permissions on sys.sql_expression_dependencies`. This task is achievable for individuals holding membership in the securityadmin fixed server role and the database_owner fixed database role within the source database of the DAC. Furthermore, exporting a DAC is feasible for those who are part of the sysadmin fixed server role or those with access to the built-in SQL Server system administrator account named sa.

On Azure SQL Database, you must grant for each database VIEW DEFINITION and SELECT permission on all tables or specific tables.

Use the Export Data-tier Application Wizard

To Export a DAC Using a Wizard

1. Connect to the instance of SQL Server, whether on-premises or in SQL Database.
2. In Object Explorer, expand the node for the instance from which you want to export the DAC.
3. Right-click the database name.
4. Select Tasks and then select Export Data-tier Application.
5. Complete the wizard dialogs:

• Introduction Page
• Export Settings Page
• Summary Page
• Progress Page
• Results Page

Introduction page

This page describes the steps for the Export Data-tier Application Wizard.

Options

Do not show this page again. - Select 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.

Export Settings page

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. Select Browse. to navigate to 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 Azure - Creates a BACPAC file in an Azure container. You must connect to an Azure container to validate this option. This option also requires that you specify a local directory for the temporary file. The temporary file will be created at the specified location and remain there after the operation.

To specify a subset of tables to export, use the Advanced option.

Summary page

Use this page to review the operation's specified source and target settings. To complete the export operation using the specified settings, select Finish. To cancel the export operation and exit the Wizard, select Cancel.

Progress page

This page displays a progress bar that indicates the status of the operation. To view detailed status, select the View details option.

Results page

This page reports the export operation's success or failure, showing each action's results. Any action that encountered an error has a link in the Result column. Select the link to view a report of the error for that action.

of the data-tier application results page." />

Select Finish to close the Wizard.

Use a .NET Framework Application

To export a DAC using the Export() method in a .Net Framework application.

1. Create an SMO Server object and set it to the instance that contains the DAC to be exported.
2. Open a ServerConnection object and connect to the same instance.
3. 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.

Limitations and restrictions

A DAC or database can only be exported from a database in SQL Database, or SQL Server 2005 (9.x) Service Pack 4 (SP4) or later.

You can't export a database with objects that aren't supported in a DAC or contain users. For more information about the types of objects supported in a DAC, see DAC Support For SQL Server Objects and Versions.

If you receive a failing with Out of Disk space message, it's advisable to configure the % TEMP % folder of the system to reside on a distinct data disk. By doing so, you can ensure sufficient space for the export process to execute smoothly, avoiding potential disk space complications.

To configure the system's %TEMP% folder:

• In Windows, open the System Properties >Properties, then select the link labeled Advanced system settings.
• In the ensuing System Properties window, navigate to the bottom and select Environment Variables.
• Under the System variables section, locate the TEMP and TMP variables, then select Edit associated with each.
• Modify the values of both variables to point to a pathway on the separate data disk you have established. For instance, if your data disk is designated as D: , set the values as D:\Temp .
• Confirm the changes by selecting OK and closing all open windows.

Related content

• Data-tier applications (DAC)
• Extract a DAC From a Database