Export an Azure SQL database or a SQL Server database to a BACPAC file

This article discusses exporting either your Azure SQL database or a SQL Server database to a BACPAC file.

Important

Azure SQL Database Automated Export is now in preview and will be retired on March 1, 2017. Starting December 1, 2016, you will no longer be able to configure automated export on any SQL database. All your existing automated export jobs will continue to work until March 1, 2017. After December 1, 2016, you can use long-term backup retention or Azure Automation to archive SQL databases periodically using PowerShell periodically according to a schedule of your choice. For a sample script, you can download the sample script from Github.

Overview

When you need to export a database for archiving or for moving to another platform, you can export the database schema and data to a BACPAC file. A BACPAC file is simply a ZIP file with an extension of BACPAC. A BACPAC file can later be stored in Azure blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database or into a SQL Server on-premises installation.

Important

If you are exporting from SQL Server as a prelude to migration to Azure SQL Database, see Migrate a SQL Server database to Azure SQL Database.

Considerations

  • For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export, or that you are exporting from a transactionally consistent copy of your Azure SQL database.
  • If you are exporting to blob storage, the maximum size of a BACPAC file is 200 GB. To archive a larger BACPAC file, export to local storage.
  • Exporting a BACPAC file to in Azure premium storage using the methods discussed in this article is not supported.
  • If the export operation from Azure SQL Database exceeds 20 hours, it may be canceled. To increase performance during export, you can:
    • Temporarily increase your service level.
    • Cease all read and write activity during the export.
    • Use a clustered index with non-null values on all large tables. Without clustered indexes, an export may fail if it takes longer than 6-12 hours. This is because the export service needs to complete a table scan to try to export entire table. A good way to determine if your tables are optimized for export is to run DBCC SHOW_STATISTICS and make sure that the RANGE_HI_KEY is not null and its value has good distribution. For details, see DBCC SHOW_STATISTICS.
Note

BACPACs are not intended to be used for backup and restore operations. Azure SQL Database automatically creates backups for every user database. For details, see Business Continuity Overview and SQL Database backups.

Next steps