快速入門:在 Azure SQL Database 中將 BACPAC 檔案匯入資料庫Quickstart: Import a BACPAC file to a database in Azure SQL Database

您可以使用 BACPAC 檔案將 SQL Server 資料庫匯入 Azure SQL Database 中的資料庫。You can import a SQL Server database into a database in Azure SQL Database using a BACPAC file. 您可以從儲存在 Azure Blob 儲存體 (僅標準儲存體) 中,或內部部署中的本機儲存體中的 BACPAC 檔案匯入資料。You can import the data from a BACPAC file stored in Azure Blob storage (standard storage only) or from local storage in an on-premises location. 若要藉由提供更多且更快速的資源來最大化匯入速度,請在匯入程序期間,將您的資料庫調整為較高的服務層級與計算大小。To maximize import speed by providing more and faster resources, scale your database to a higher service tier and compute size during the import process. 然後,您可以在匯入成功後相應減少。You can then scale down after the import is successful.

注意

資料庫匯入後的相容性層級會以來源資料庫的相容性層級為基礎。The imported database's compatibility level is based on the source database's compatibility level.

重要

匯入資料庫後,您可以選擇於目前的相容性層級 (針對 AdventureWorks2008R2 資料庫為層級 100) 或更高層級運作資料庫。After importing your database, you can choose to operate the database at its current compatibility level (level 100 for the AdventureWorks2008R2 database) or at a higher level. 如需於特定相容性層級操作資料庫的含意與選項詳細資訊,請參閱 ALTER DATABASE 相容性層級 (ALTER DATABASE Compatibility Level)For more information on the implications and options for operating a database at a specific compatibility level, see ALTER DATABASE Compatibility Level. 如需相容性層級其他相關資料庫等級設定的資訊,另請參閱 ALTER DATABASE 範圍組態 (ALTER DATABASE SCOPED CONFIGURATION)See also ALTER DATABASE SCOPED CONFIGURATION for information about additional database-level settings related to compatibility levels.

在 Azure 入口網站中從 BACPAC 檔案匯入Import from a BACPAC file in the Azure portal

Azure 入口網站「僅」支援在 Azure SQL Database 中建立單一資料庫,且「僅能」從儲存在 Azure Blob 儲存體中的 BACPAC 檔案建立。The Azure portal only supports creating a single database in Azure SQL Database and only from a BACPAC file stored in Azure Blob storage.

目前不支援使用 Azure PowerShell 從 BACPAC 檔案將資料庫移轉至受控實例Migrating a database into a managed instance from a BACPAC file using Azure PowerShell is not currently supported. 請改用 SQL Server Management Studio 或 SQLPackage。Use SQL Server Management Studio or SQLPackage instead.

注意

處理透過 Azure 入口網站或 PowerShell 提交的匯入/匯出要求的機器, 必須儲存 BACPAC 檔案, 以及資料層應用程式架構 (DacFX) 所產生的暫存檔案。Machines processing import/export requests submitted through the Azure portal or PowerShell need to store the BACPAC file as well as temporary files generated by the Data-Tier Application Framework (DacFX). 所需的磁碟空間在大小相同的資料庫之間有很大的差異, 而且可能需要最多3倍資料庫大小的磁碟空間。The disk space required varies significantly among databases with the same size and can require disk space up to 3 times the size of the database. 執行匯入/匯出要求的機器僅具有450GB 本機磁碟空間。Machines running the import/export request only have 450GB local disk space. 因此, 某些要求可能會因錯誤There is not enough space on the disk而失敗。As a result, some requests may fail with the error There is not enough space on the disk. 在此情況下, 因應措施是在具有足夠本機磁碟空間的電腦上執行 sqlpackage。In this case, the workaround is to run sqlpackage.exe on a machine with enough local disk space. 我們建議使用SqlPackage來匯入/匯出大於150GB 的資料庫, 以避免發生此問題。We encourage using SqlPackage to import/export databases larger than 150GB to avoid this issue.

  1. 若要使用 Azure 入口網站從 BACPAC 檔案匯入至單一資料庫,請開啟適當的資料庫伺服器頁面,然後在工具列上選取 [匯入資料庫]。To import from a BACPAC file into a new single database using the Azure portal, open the appropriate database server page and then, on the toolbar, select Import database.

    資料庫匯入1

  2. 選取 BACPAC 檔案的儲存體帳戶及容器,然後選取要匯入的 BACPAC 檔案。Select the storage account and the container for the BACPAC file and then select the BACPAC file from which to import.

  3. 指定新資料庫的大小 (通常與原始資料庫相同),並提供目的地 SQL Server 認證。Specify the new database size (usually the same as origin) and provide the destination SQL Server credentials. 如需新 Azure SQL 資料庫可能之值的清單,請參閱建立資料庫For a list of possible values for a new Azure SQL database, see Create Database.

    資料庫匯入2

  4. 按一下 [確定]。Click OK.

  5. 若要監視匯入進度,請開啟資料庫的伺服器頁面,然後在 [設定] 下選取 [匯入/匯出記錄]。To monitor an import's progress, open the database's server page, and, under Settings, select Import/Export history. 匯入成功時,會處於 [已完成] 狀態。When successful, the import has a Completed status.

    資料庫匯入狀態

  6. 若要確認資料庫伺服器上的資料庫為線上狀態,請選取 [SQL 資料庫],並確認新的資料庫為 [線上] 狀態。To verify the database is live on the database server, select SQL databases and verify the new database is Online.

使用 SqlPackage 從 BACPAC 檔案匯入Import from a BACPAC file using SqlPackage

若要使用 SqlPackage 命令列公用程式匯入 SQL Server 資料庫,請參閱匯入參數和屬性To import a SQL Server database using the SqlPackage command-line utility, see import parameters and properties. SqlPackage 具有最新的 SQL Server Management Studio適用於 Visual Studio 的 SQL Server Data ToolsSqlPackage has the latest SQL Server Management Studio and SQL Server Data Tools for Visual Studio. 您也可以從 Microsoft 下載中心下載最新的 SqlPackageYou can also download the latest SqlPackage from the Microsoft download center.

為了規模和效能,我們建議在大部分生產環境中使用 SqlPackage,而不使用 Azure 入口網站。For scale and performance, we recommend using SqlPackage in most production environments rather than using the Azure portal. 如需 SQL Server 客戶諮詢小組部落格中有關使用 BACPAC 檔案進行移轉的主題,請參閱使用 BACPAC 檔案從 SQL Server 移轉至 Azure SQL Database (英文)。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.

基於調整能力和效能,建議在大部分的生產環境中均應使用 SqlPackage。For scale and performance, we recommend using SqlPackage in most production environments. 如需 SQL Server 客戶諮詢小組部落格中有關使用 BACPAC 檔案進行移轉的主題,請參閱使用 BACPAC 檔案從 SQL Server 移轉至 Azure SQL DatabaseFor a SQL Server Customer Advisory Team blog about migrating using BACPAC files, see Migrating from SQL Server to Azure SQL Database using BACPAC Files.

下列 SqlPackage 命令會將 AdventureWorks2008R2 資料庫從本機儲存體匯入至名為 mynewserver20170403 的 Azure SQL Database 伺服器。The following SqlPackage command imports the AdventureWorks2008R2 database from local storage to an Azure SQL Database server called mynewserver20170403. 此命令會建立名為 myMigratedDatabase、且具有進階服務層級和 P6 服務目標的新資料庫。It creates a new database called myMigratedDatabase with a Premium service tier and a P6 Service Objective. 請針對您的環境適當變更這些值。Change these values as appropriate for your environment.

SqlPackage.exe /a:import /tcs:"Data Source=mynewserver20170403.database.windows.net;Initial Catalog=myMigratedDatabase;User Id=<your_server_admin_account_user_id>;Password=<your_server_admin_account_password>" /sf:AdventureWorks2008R2.bacpac /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P6

重要

若要連線到從公司防火牆後方管理單一資料庫的 SQL Database 伺服器,該防火牆必須開啟連接埠 1433。To connect to a SQL Database server managing a single database from behind a corporate firewall, the firewall must have port 1433 open. 若要連線到受控執行個體,您必須有點對站連線或快速路由連線。To connect to a managed instance, you must have a point-to-site connection or an express route connection.

此範例說明如何使用 SqlPackage 與 Active Directory 通用驗證來匯入資料庫。This example shows how to import a database using SqlPackage with Active Directory Universal Authentication.

SqlPackage.exe /a:Import /sf:testExport.bacpac /tdn:NewDacFX /tsn:apptestserver.database.windows.net /ua:True /tid:"apptest.onmicrosoft.com"

使用 PowerShell 從 BACPAC 檔案匯入至單一資料庫Import into a single database from a BACPAC file using PowerShell

注意

受控實例目前不支援使用 AZURE POWERSHELL 從 BACPAC 檔案將資料庫移轉至實例資料庫。A managed instance does not currently support migrating a database into an instance database from a BACPAC file using Azure PowerShell. 若要匯入至受控執行個體,請使用 SQL Server Management Studio 或 SQLPackage。To import into a managed instance, use SQL Server Management Studio or SQLPackage.

注意

處理透過入口網站或 Powershell 提交的匯入/匯出要求的機器, 必須儲存 bacpac 檔案, 以及資料層應用程式架構 (DacFX) 所產生的暫存檔案。The machines processing import/export requests submitted through portal or Powershell need to store the bacpac file as well as temporary files generated by Data-Tier Application Framework (DacFX). 所需的磁碟空間在相同大小的資料庫之間有很大的差異, 而且最多可能需要3倍的資料庫大小。The disk space required varies significantly among DBs with same size and can take up to 3 times of the database size. 執行匯入/匯出要求的機器僅具有450GB 本機磁碟空間。Machines running the import/export request only have 450GB local disk space. 因此, 某些要求可能會失敗, 並出現「磁碟空間不足」錯誤。As result, some requests may fail with “There is not enough space on the disk” error. 在此情況下, 因應措施是在具有足夠本機磁碟空間的電腦上執行 sqlpackage。In this case, the workaround is to run sqlpackage.exe on a machine with enough local disk space. 匯入/匯出大於150GB 的資料庫時, 請使用SqlPackage來避免此問題。When importing/exporting databases larger than 150GB, use SqlPackage to avoid this issue.

注意

本文已更新為使用新的 Azure PowerShell Az 模組。This article has been updated to use the new Azure PowerShell Az module. AzureRM 模組在至少 2020 年 12 月之前都還會持續收到錯誤 (Bug) 修正,因此您仍然可以持續使用。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要深入了解新的 Az 模組和 AzureRM 的相容性,請參閱新的 Azure PowerShell Az 模組簡介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 如需 Az 模組安裝指示,請參閱安裝 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

重要

Azure SQL Database 仍然支援 PowerShell Azure Resource Manager 模組, 但所有未來的開發都是針對 Az .Sql 模組。The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. 如需這些 Cmdlet, 請參閱AzureRMFor these cmdlets, see AzureRM.Sql. Az 模組和 AzureRm 模組中命令的引數本質上完全相同。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

使用AzSqlDatabaseImport Cmdlet 將匯入資料庫要求提交至 Azure SQL Database 服務。Use the New-AzSqlDatabaseImport cmdlet to submit an import database request to the Azure SQL Database service. 匯入可能需要一些時間才能完成,視資料庫大小而定。Depending on database size, the import may take some time to complete.

$importRequest = New-AzSqlDatabaseImport 
   -ResourceGroupName "<your_resource_group>" `
   -ServerName "<your_server>" `
   -DatabaseName "<your_database>" `
   -DatabaseMaxSizeBytes "<database_size_in_bytes>" `
   -StorageKeyType "StorageAccessKey" `
   -StorageKey $(Get-AzStorageAccountKey -ResourceGroupName "<your_resource_group>" -StorageAccountName "<your_storage_account").Value[0] `
   -StorageUri "https://myStorageAccount.blob.core.windows.net/importsample/sample.bacpac" `
   -Edition "Standard" `
   -ServiceObjectiveName "P6" `
   -AdministratorLogin "<your_server_admin_account_user_id>" `
   -AdministratorLoginPassword $(ConvertTo-SecureString -String "<your_server_admin_account_password>" -AsPlainText -Force)

您可以使用AzSqlDatabaseImportExportStatus Cmdlet 來檢查匯入的進度。You can use the Get-AzSqlDatabaseImportExportStatus cmdlet to check the import's progress. 如果在要求後立即執行此 Cmdlet,通常會傳回 Status:InProgressRunning the cmdlet immediately after the request usually returns Status: InProgress. 當您看見 Status:Succeeded 時,即表示匯入已完成。The import is complete when you see Status: Succeeded.

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

提示

如需其他指令碼範例,請參閱從 BACPAC 檔案匯入資料庫For another script example, see Import a database from a BACPAC file.

限制Limitations

不支援匯入至彈性集區中的資料庫。Importing to a database in elastic pool isn't supported. 您可以將資料匯入到單一資料庫,然後將資料庫移到彈性集區。You can import data into a single database and then move the database to an elastic pool.

使用精靈匯入Import using wizards

您也可以使用這些精靈。You can also use these wizards.

後續步驟Next steps