Share via

How to Use Data-Tier Application Import and Export with a Windows Azure SQL Database

  • Article

This article describes the import and export features in the Microsoft SQL Server “Denali” Data-tier Application Framework 2012 RC0 to import and export data with SQL Database. The import and export features provide the ability to retrieve
and restore an entire database, including schema and data, in a single file
operation.

Note

If you wish to contribute to this page, use theEdittab at the top (sign-in required). If you wish to provide feedback for this documentation please either send e-mail toazuredocs@microsoft.comor use the Comment field at the bottom of this page (sign-in required). To provide feedback or ask questions about using Data-Tier Application Import or Export with SQL Database, use the MSDN forums athttp://social.msdn.microsoft.com/Forums/en-US/sqlazurelabssupport/threads/.

Microsoft SQL Server Data-tier Application Framework 2012 RC0

The SQL Server Data-tier Application (DAC) framework is a component based on the .NET Framework that provides application lifecycle services for database development and management. Application lifecycle services include extract, build, deploy, upgrade, import, and export for data-tier applications in SQL Azure, SQL Server code named ‘Denali’ CTP 3, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005, and limited support for SQL Server 2000.

For an introduction to and more information on the DAC Framework, this whitepaper is available:

http://msdn.microsoft.com/en-us/library/ff381683(SQL.100).aspx .

Installation

In order to use the new import and export services, you will need the .NET 4 runtime installed.

With .NET 4 installed, you will then install the SQL Server 2012 RC0 Manageability Tool Kit on your machine. The packages for the redistributable components are available at the following links:

The SQL DAC Examples demonstrate the SQL SErver DAC API usage. You can get a copy at http:/sqldacexamples.codeplex.com.

Usage

You can leverage the import and export services by using DacCli.EXE in the SQL DAC Examples which is provided as an example only, or by using the
public methods on the Microsoft.SQLServer.Management.DAC.dll in theDACStore class.

Sample Commands

Assume a database exists that is running on SQL Server 2008 R2, which a user has federated (or integrated security) access to. The database can be exported to a “.bacpac” file by calling the sample EXE with the following arguments:

DacCli.exe -s serverName -d databaseName -f C:\filePath\exportFileName.bacpac -x -e

Once exported, the export file can be imported to a SQL Database with:

DacCli.exe -s serverName.database.windows.net -d databaseName -f C:\filePath\exportFileName.bacpac -i -u userName -p password

A DAC database running in SQL Server or SQL Database can be unregistered and
dropped with:

DacCli.exe -s serverName.database.windows.net -drop databaseName -u userName -p password

You can also just as easily export a SQL Database to a local “.bacpac”
export file and import it into SQL Server.

DAC Import/Export FAQ

What is in a ���.bacpac” export file?

The export file contains all supported database schema objects and table data in a single package. Schema objects include logins, users, schemas, tables, columns, constraints, indexes, views, stored procedures, functions, triggers, and other objects relevant to the definition of a database. All SQL Database data
types except SQL variant are supported in this CTP.

Is an export file a backup?

No. The export file does not have a transaction log or historical data. The export file simply contains the contents of a SELECT * for any given table and is not transactionally consistent by itself.

What’s the best way to create a transactionally consistent database archive?

You may choose to provide transactional consistency for an export operation by creating a database copy on SQL Database and then exporting from the copy. If
you’re exporting from on-premise SQL Servers, you can isolate the database by
placing it in single-user mode or read-only mode, or by exporting from a
database snapshot.

What versions of SQL Server can I export from?

  • SQL Database

  • SQL Server “Denali”

  • SQL Server 2008 R2

  • SQL Server 2008

  • SQL Server 2005

  • SQL Server 2000

What versions of SQL Server can I import to?

  • SQL Database

  • SQL Server “Denali”

  • SQL Server 2008 R2

  • SQL Server 2008 (SP1 and newer)

  • SQL Server 2005 (SP4 and newer)

Do I have to use the .EXE?

For developers, the import and export services are provided as public methods on theMicrosoft.SQLServer.Management.DAC.dll in the DACStore class and can be called directly without the EXE provided above. The source for the executable will be made available as a sample on how to consume these new services within your own applications.

Why do I see “Cannot extract a DAC from database {0} because it has objects not supported in a DAC, or is missing dependencies.”?

The DAC Framework does not currently support all SQL Server schema objects. As a result, when exporting from database servers other than SQL Database, you may
experience this error. You will need to remove the object identified in the
error as that object is not supported by the DAC application model and likely
cannot be deployed to SQL Database.

Can I specify the type or size of SQL Database during an import?

Yes, you can specify the edition and size of database to import to by setting flags for the EXE. The following sample will create a business edition database with a maximum size of 30 GB:

DacImportExportCli.exe -s serverName.database.windows.net -d databaseName -f C:\filePath\exportFileName.bacpac -i –edition business –size 30 -u userName -p password

My Azure import fails because of connection issues, what can I do?

Unfortunately, connections are not always reliable, especially Internet connections. In the event of a failed import or export, you can always try again.

Import and export operations are currently atomic and cannot be resumed. Look for future announcements and updates on enhancements on import and export services for SQL Database.

What is the largest supported database?

The export and import services do not have a specific database size limitation, but SQL Database does. For the most recent database size capabilities
for SQL Database, please follow this link: http://msdn.microsoft.com/en-us/library/ee621788.aspx

I imported my database and dropped it, so why can’t I import it again?

The DAC Framework supports versioned deployments; which means that when a DAC service is used to create or upgrade a database, the database schema is registered for future reference. You will be unable to re-register database schema for the same database and therefore have to remove the registration before importing again. The provided EXE removes DAC registrations with the –drop command, even for databases that no longer exist. Sample:

DacImportExportCli.exe -s serverName.database.windows.net -drop databaseName -u userName -p password

I receive a failure when importing to SQL Database, how can I resolve this?

Imports to SQL Database can fail if the database contains diagrams and support
objects. You can often resolve this by dropping the diagrams and support objects
before exporting the database. Use the following to drop the diagrams and
associated objects, then export the database again and then try to import into
SQL Database.

USE DatabaseName

DROP PROCEDURE sp_alterdiagram

DROP PROCEDURE sp_upgraddiagrams

DROP PROCEDURE sp_upgraddiagrams

DROP PROCEDURE sp_helpdiagrams

DROP PROCEDURE sp_helpdiagramdefinition

DROP PROCEDURE sp_creatediagram

DROP PROCEDURE sp_renamediagram

DROP PROCEDURE sp_alterdiagram

DROP PROCEDURE sp_dropdiagram

DROP FUNCTION fn_diagramobjects

DROP TABLE sysdiagrams

DROP PROCEDURE dt_properties

How do I get help?

Please use the MSDN forums to ask questions or give feedback.