将 Azure SQL 数据库导出到 BACPAC 文件Export an Azure SQL database to a BACPAC file

如果需要为存档或移动到其他平台而导出数据库,可将数据库架构和数据导出到 BACPAC 文件。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. BACPAC 文件是一个扩展名为 BACPAC 的 ZIP 文件,它包含来自 SQL Server 数据库的元数据和数据。A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from a SQL Server database. 可将 BACPAC 文件存储在 Azure Blob 存储中或存储在本地位置的本地存储中,之后可以导入回 Azure SQL 数据库或 SQL Server 本地安装。A BACPAC file can 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.

导出 Azure SQL 数据库时的注意事项Considerations when exporting an Azure SQL database

  • 为保证导出的事务处理方式一致,必须确保导出期间未发生写入活动,或者正在从 Azure SQL 数据库的事务处理方式一致性副本中导出。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.

  • 如果是导出到 Blob 存储,则 BACPAC 文件的最大大小为 200 GB。If you are exporting to blob storage, the maximum size of a BACPAC file is 200 GB. 若要存档更大的 BACPAC 文件,请导出到本地存储。To archive a larger BACPAC file, export to local storage.

  • 不支持使用本文所述方法将 BACPAC 文件导出到 Azure 高级存储。Exporting a BACPAC file to Azure premium storage using the methods discussed in this article is not supported.

  • 目前不支持有防火墙的存储。Storage behind a firewall is currently not supported.

  • 如果从 Azure SQL 数据库进行的导出操作超过 20 个小时,系统可能会取消该操作。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 compute size.
    • 在导出期间终止所有读取和写入活动。Cease all read and write activity during the export.
    • 对所有大型表上的非 null 值使用聚集索引Use a clustered index with non-null values on all large tables. 如果不使用聚集索引,当时间超过 6-12 个小时时,导出可能会失败。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. 确认表是否针对导出进行优化的一个好方法是,运行 DBCC SHOW_STATISTICS 并确保 RANGE_HI_KEY 不是 null 并且值分布良好。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. 有关详细信息,请参阅 DBCC SHOW_STATISTICSFor details, see DBCC SHOW_STATISTICS.

Note

BACPAC 不能用于备份和还原操作。BACPACs are not intended to be used for backup and restore operations. Azure SQL 数据库会自动为每个用户数据库创建备份。Azure SQL Database automatically creates backups for every user database. 有关详细信息,请参阅业务连续性概述SQL 数据库备份For details, see business continuity overview and SQL Database backups.

使用 Azure 门户导出到 BACPAC 文件Export to a BACPAC file using the Azure portal

  1. 若要使用 Azure 门户导出数据库,请打开数据库页,并在工具栏上单击“导出”。To export a database using the Azure portal, open the page for your database and click Export on the toolbar.

    数据库导出

  2. 指定 BACPAC 文件名,为导出选择现有的 Azure 存储帐户和容器,然后提供用于访问源数据库的相应凭据。Specify the BACPAC filename, select an existing Azure storage account and container for the export, and then provide the appropriate credentials for access to the source database.

    数据库导出

  3. 单击 “确定”Click OK.

  4. 若要监视导出操作的进度,请打开包含待导出数据库的 SQL 数据库服务器的相应页面。To monitor the progress of the export operation, open the page for the SQL Database server containing the database being exported. 向下滚动到“设置”,并单击“导入/导出历史记录”。Under to Settings and then click Import/Export history.

    导出历史记录

使用 SQLPackage 实用工具导出到 BACPAC 文件Export to a BACPAC file using the SQLPackage utility

若要使用 SqlPackage 命令行实用工具导出 SQL 数据库,请参阅导出参数和属性To export a SQL database using the SqlPackage command-line utility, see Export parameters and properties. SQLPackage 实用工具附带最新版本的 SQL Server Management Studio用于 Visual Studio 的 SQL Server Data Tools,也可以直接从 Microsoft 下载中心下载最新版本的 SqlPackageThe SQLPackage utility ships with the latest versions of SQL Server Management Studio and SQL Server Data Tools for Visual Studio, or you can download the latest version of SqlPackage directly from the Microsoft download center.

在大多数生产环境中,建议使用 SQLPackage 实用工具来实现缩放和性能。We recommend the use of the SQLPackage utility for scale and performance in most production environments. 如需 SQL Server 客户顾问团队编写的有关使用 BACPAC 文件进行迁移的博客,请参阅 Migrating from SQL Server to Azure SQL Database using BACPAC Files(使用 BACPAC 文件从 SQL Server 迁移到 Azure SQL 数据库)。For a SQL Server Customer Advisory Team blog about migrating using BACPAC files, see Migrating from SQL Server to Azure SQL Database using BACPAC Files.

此示例演示如何通过 Active Directory 通用身份验证,使用 SqlPackage.exe 来导出数据库:This example shows how to export a database using SqlPackage.exe with Active Directory Universal Authentication:

SqlPackage.exe /a:Export /tf:testExport.bacpac /scs:"Data Source=apptestserver.database.chinacloudapi.cn;Initial Catalog=MyDB;" /ua:True /tid:"apptest.partner.onmschina.cn"

使用 SQL Server Management Studio (SSMS) 导出到 BACPAC 文件Export to a BACPAC file using SQL Server Management Studio (SSMS)

SQL Server Management Studio 的最新版本提供一个向导,可将 Azure SQL 数据库导出到 BACPAC 文件。The newest versions of SQL Server Management Studio provides a wizard to export an Azure SQL database to a BACPAC file. 请参阅导出数据层应用程序See the Export a Data-tier Application.

使用 PowerShell 导出到 BACPAC 文件Export to a BACPAC file using PowerShell

使用 New-AzSqlDatabaseExport cmdlet 向 Azure SQL 数据库服务提交导出数据库请求。Use the New-AzSqlDatabaseExport cmdlet to submit an export database request to the Azure SQL Database service. 根据数据库的大小,导出操作可能需要一些时间才能完成。Depending on the size of your database, the export operation may take some time to complete.

$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
  -DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
  -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

若要检查导出请求的状态,请使用 Get-AzSqlDatabaseImportExportStatus cmdlet。To check the status of the export request, use the Get-AzSqlDatabaseImportExportStatus cmdlet. 如果在提交请求后立即运行此命令,通常会返回“状态: 正在进行”。Running this immediately after the request usually returns Status: InProgress. 显示“状态: 成功”时,表示导出完毕。When you see Status: Succeeded the export is complete.

$exportStatus = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
[Console]::Write("Exporting")
while ($exportStatus.Status -eq "InProgress")
{
    Start-Sleep -s 10
    $exportStatus = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
    [Console]::Write(".")
}
[Console]::WriteLine("")
$exportStatus

后续步骤Next steps