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.
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.
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.
- You can export your Azure SQL database using the Azure portal, PowerShell, SQLPackage, or SQL Server Management Studio.
- You can export a SQL Server database using PowerShell, SQLPackage, or SQL Server Management Studio.
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.
- 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.
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.
- For a discussion of the entire SQL Server database migration process, see Migrate a SQL Server database to Azure SQL Database.
- For an overview of copying a database within Azure, see also Copying an Azure SQL database.
- You can copy your Azure SQL database within Azure using the Azure portal, PowerShell, or Transact-SQL.