Simplified Import and Export of Data - SQL Azure Team Blog - Site Home - MSDN Blogs

Simplified Import and Export of Data

Simplified Import and Export of Data

  • Comments 8

Importing and exporting data between on-premises SQL Server and SQL Azure just got a lot easier, and you can get started today with the availability of the Microsoft SQL Server "Denali" Data-tier Application (DAC) Framework v2.0 Feature Pack CTP.  Let's call this the DAC framework from this point on J.  To learn more about DAC, you can read this whitepaper.

If you're eager to try it out, go to the SQL Azure Labs page; otherwise read on for a bit to learn more.

There are 3 important things about this update to the DAC framework:

  • 1. New import & export feature: This DAC CTP introduces an import and export feature that lets you store both database schema and data into a single file with a ".bacpac" file extension, radically simplifying data import and export between SQL Server and SQL Azure databases with the use of easy commands.
  • 2. Free: This functionality will be shipping in all editions of the next version of SQL Server "Denali"-including free SKUs-and everyone will be able to download it.
  • 3. The Future = Hybrid Applications Using SQL Server + SQL Azure: Freeing information to move back and forth from on-premises SQL Server and SQL Azure to create hybrid applications is the future of data. The tools that ship with SQL Server "Denali" will use the DAC framework to enable data movement as part of normal management tasks. 

The Data-tier Application (DAC) framework is a collection of database schema and data management libraries that are strategic to database management in SQL Server and SQL Azure.  In this CTP, the new import and export feature allow for the retrieval and restoration of a full database, including schema and data, in a single operation. 

If you want to archive or move your database between SQL Server versions and SQL Azure, you can export a target database to a single export file, which contains both database schema and data in a single file.  Also included are logins, users, tables, columns, constraints, indexes, views, stored, procedures, functions, and triggers.  Once a database has been exported, users can import the file with the new import operation.  

This release of the import and export feature is a preview for fully supported archival and migration capability of SQL Azure and SQL Server databases.  In coming months, additional enhancements will be made to the Windows Azure Platform management portal. Tools and management features shipping in upcoming releases of SQL Server and SQL Azure will have more capabilities powered by DAC, providing increased symmetry in what you can accomplish on-premises and in the cloud.

How Do I Use It?

Assume a database exists that is running within an on-premises SQL Server 2008 R2 instance that a user has access to.  You can export the database to a single ".bacpac" file by going to a command line and typing:

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

Once exported, the newly created file with the extension ".bacpac" can be imported to a SQL Azure database if you type:

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

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

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

You can also just as easily export a SQL Azure database to a local export file and import it into SQL Server.

How Should I Use the Import and Export Features?

It's important to note that export is not a recommended backup mechanism for SQL Azure databases. (We're working on that. so look for an update in the near future).  The export file doesn't store 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.

However, you can create a transactionally consistent export by creating a copy of your SQL Azure database and then doing a DAC export on that.  This article has details on how you can quickly create a copy of your SQL Azure database.  If you export 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.

We are considering additional enhancements to make it easier to export or restore SQL Azure databases with export files stored in cloud storage - so stay tuned.

Tell Us What You Think

We're really interested to hear your feedback and learn about your experience using this new functionality.  Check out the SQL Azure forum and send us your thoughts here.

Check out the SQL Azure Labs page for installation and usage instructions, and frequently asked questions (FAQ). 

 

Thanks!

-Steve

  • "It's important to note that export is not a recommended backup mechanism for SQL Azure databases. (We're working on that. so look for an update in the near future)."

    This is really what we all want -- a good backup where we can simply export a file and archive it.  All of these various synch and dac frameworks are missing the point completely.  I know they are meant to solve different problems, but I think backup is a much bigger problem and these come close but not close enough.  Please, true backup cannot come soon enough!

  • Hi Pete,

    Thanks for the feedback. Currently, you can archive your SQL Azure data is to perform a db copy operation to another database.  We're keenly aware of backup/restore scenarios and expect some news about that in the near future.

    Appreciate the feedback!

    -Steve

  • Will this utility allow us to import/export data only without the need for the schema, users, etc.?

    Also, I'm currently using bcp to upload data to SQL Azure. One of my tables has 1.5 million records, with a data space of 503 MB and an index space of 1,559 MB.  I received a connection timeout when using bcp at chunks of 1,000 rows and had to retry in 500 row chunks.  That worked but took over an hour.  While it is currently working, I'm wondering if the DAC Framework will be faster?

    Thanks,

    Chris

  • This import/export utility did a good job until last week; since then we get an exception when using the tool with SQLAzure, with SQLServer it is still working. So something must have been changed in SQLAzure that prevents the import/export-utility from working.

    Would be great to get it working again...

    Thanks,

    Joerg

  • @Joerg Mehlhorn there is an update coming to the tool.  Keep checking the Azure labs site, and the codeplex project.

  • sqldacexamples.codeplex.com is the codeplex site for the most current version of the CLI Tool.

  • @Jason : Thanks for your help, the new version (in conjunction with CTP3-components) works! Components can be found at:

    social.msdn.microsoft.com/.../dd79696f-88fd-4c52-912d-a50f35ab01ab

  • Hello,everyone,I have installed all the needed components for DacImportExportCli.exe,but I still can not open or run it,who can tell me why and what else should I do ?

Page 1 of 1 (8 items)
Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post