匯入 BACPAC 檔案以建立新的使用者資料庫Import a BACPAC File to Create a New User Database

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

匯入資料層應用程式 (DAC) 檔案 (.bacpac 檔案),可在新的 Database EngineDatabase Engine 執行個體上,建立原始資料庫連同其資料的複本,或將該檔案匯入 Azure SQL DatabaseAzure SQL DatabaseImport a data-tier application (DAC) file - a .bacpac file - to create a copy of the original database, with the data, on a new instance of the Database EngineDatabase Engine, or to Azure SQL DatabaseAzure SQL Database. 匯出-匯入作業可以進行合併以在執行個體之間移轉 DAC 或資料庫,或建立邏輯備份 (例如建立 SQL DatabaseSQL Database中所部署資料庫的內部部署複本)。Export-import operations can be combined to migrate a DAC or database between instances, or to create a logical backup, such as creating an on-premise copy of a database deployed in SQL DatabaseSQL Database.

開始之前Before You Begin

匯入程序會使用兩個階段來建立新的 DAC。The import process builds a new DAC in two stages.

  1. 匯入會使用儲存在匯出檔案中的 DAC 定義,建立新的 DAC 及相關聯的資料庫,其方式相當於 DAC 部署從 DAC 封裝檔案中的定義建立新的 DAC。The import creates a new DAC and associated database using the DAC definition stored in the export file, the same way a DAC deploy creates a new DAC from the definition in a DAC package file.

  2. 匯入會從匯出檔案大量複製資料。The import bulk copies in the data from the export file.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

SQL Server 公用程式SQL Server Utility

如果您將 DAC 匯入至資料庫引擎執行個體,下次從執行個體將公用程式收集組傳送到公用程式控制點時,匯入的 DAC 就會合併至 SQL Server 公用程式。If you import a DAC to an instance of the Database Engine, the imported DAC is incorporated into the SQL Server Utility the next time the utility collection set is sent from the instance to the utility control point. 然後 DAC 會出現在 [公用程式總管] Management StudioManagement Studio [部署的資料層應用程式] 節點中,並在 詳細資料頁面中報告。The DAC will then be present in the Deployed Data-tier Applications node of the Management StudioManagement Studio Utility Explorer and reported in the Deployed Data-tier Applications details page.

資料庫選項和設定Database Options and Settings

根據預設,匯入期間建立的資料庫將會擁有 CREATE DATABASE 陳述式中的所有預設值,但是資料庫定序和相容性層級會設定為 DAC 匯出檔案中所定義的值。By default, the database created during the import will have all of the default settings from the CREATE DATABASE statement, except that the database collation and compatibility level are set to the values defined in the DAC export file. DAC 匯出檔案使用原始資料庫中的值。A DAC export file uses the values from the original database.

某些資料庫選項 (例如 TRUSTWORTHY、DB_CHAINING 和 HONOR_BROKER_PRIORITY) 無法在匯入過程中調整。Some database options, such as TRUSTWORTHY, DB_CHAINING, and HONOR_BROKER_PRIORITY, cannot be adjusted as part of the import process. 實體屬性 (如檔案群組數目或檔案數目和大小) 無法在匯入過程中更改。Physical properties, such as the number of filegroups, or the numbers and sizes of files cannot be altered as part of the import process. 匯入完成之後,您可以使用 ALTER DATABASE 陳述式、 SQL Server Management StudioSQL Server Management StudioSQL ServerSQL Server PowerShell 來調整資料庫。After the import completes, you can use the ALTER DATABASE statement, SQL Server Management StudioSQL Server Management Studio, or SQL ServerSQL Server PowerShell to tailor the database. 如需詳細資訊,請參閱 DatabasesFor more information, see Databases.

限制事項Limitations and restrictions

DAC 可匯入至 SQL DatabaseSQL Database或執行 Database EngineDatabase Engine Service Pack 4 (SP4) 或更新版本的 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 執行個體。A DAC can be imported to SQL DatabaseSQL Database, or an instance of the Database EngineDatabase Engine running SQL Server 2005 (9.x)SQL Server 2005 (9.x) Service Pack 4 (SP4) or later. 如果您從更新版本匯出 DAC,則 DAC 可能會包含 SQL Server 2005 (9.x)SQL Server 2005 (9.x)不支援的物件。If you export a DAC from a higher version, the DAC may contain objects not supported by SQL Server 2005 (9.x)SQL Server 2005 (9.x). 您無法將這些 DAC 部署至 SQL Server 2005 (9.x)SQL Server 2005 (9.x)執行個體。You cannot deploy those DACs to instances of SQL Server 2005 (9.x)SQL Server 2005 (9.x).

PrerequisitesPrerequisites

建議您不要匯入來源不明或來源不受信任的 DAC 匯出檔案。We recommend that you do not import a DAC export file from unknown or untrusted sources. 這類檔案可能包含惡意程式碼,因此可能會執行非預期的 Transact-SQL 程式碼,或是修改結構描述而造成錯誤。Such files could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema. 在您使用來源不明或來源不受信任的匯出檔案之前,請解除封裝 DAC 並檢查程式碼,例如預存程序和其他使用者定義的程式碼。Before you use an export file from an unknown or untrusted source, unpack the DAC and examine the code, like stored procedures and other user-defined code. 如需有關如何執行這些檢查的詳細資訊,請參閱< Validate a DAC Package>。For more information about how to perform these checks, see Validate a DAC Package.

SecuritySecurity

為了提高安全性,SQL Server 驗證登入會儲存在 DAC 匯出檔案中,而且沒有密碼。To improve security, SQL Server Authentication logins are stored in a DAC export file without a password. 當您匯入檔案之後,此登入會建立為停用的登入,而且會產生密碼。When the file is imported, the login is created as a disabled login with a generated password. 若要啟用登入,請使用具有 ALTER ANY LOGIN 權限的登入進行登入,並使用 ALTER LOGIN 來啟用登入,然後指派可以傳達給使用者的新密碼。To enable the logins, sign in using a login that has ALTER ANY LOGIN permission and use ALTER LOGIN to enable the login and assign a new password that can be communicated to the user. Windows 驗證登入不需要這項處理,因為這類登入的密碼不是由 SQL Server 所管理。This is not needed for Windows Authentication logins because their passwords are not managed by SQL Server.

權限Permissions

只有 系統管理員 (sysadmin)serveradmin 固定伺服器角色的成員,或是具有 dbcreator 固定伺服器角色且擁有 ALTER ANY LOGIN 權限的登入,才能匯入 DAC。A DAC can only be imported by members of the sysadmin or serveradmin fixed server roles, or by logins that are in the dbcreator fixed server role and have ALTER ANY LOGIN permissions. 內建的 SQL ServerSQL Server 系統管理員帳戶 (名稱為 sa ) 也可以匯入 DAC。The built-in SQL ServerSQL Server system administrator account named sa can also import a DAC. 將具有登入的 DAC 匯入至 SQL DatabaseSQL Database ,需要 loginmanager 或 serveradmin 角色的成員資格。Importing a DAC with logins to SQL DatabaseSQL Database requires membership in the loginmanager or serveradmin roles. 將不具有登入的 DAC 匯入至 SQL DatabaseSQL Database ,需要 dbmanager 或 serveradmin 角色的成員資格。Importing a DAC without logins to SQL DatabaseSQL Database requires membership in the dbmanager or serveradmin roles.

使用匯入資料層應用程式精靈Using the Import Data-tier Application Wizard

若要啟動此精靈,請使用下列步驟:To launch the wizard, use the following steps:

  1. 連接到 SQL ServerSQL Server執行個體 (不論是內部部署或在 SQL DatabaseSQL Database中)。Connect to the instance of SQL ServerSQL Server, whether on-premise or in SQL DatabaseSQL Database.

  2. [物件總管][資料庫] 上按一下滑鼠右鍵,然後選取 [匯入資料層應用程式] 功能表項目啟動精靈。In Object Explorer, right-click on Databases, and then select the Import Data-tier Application menu item to launch the wizard.

  3. 完成精靈對話方塊:Complete the wizard dialogs:

簡介頁面Introduction Page

此頁面描述的是資料層應用程式匯入精靈的步驟。This page describes the steps for the Data-tier Application Import Wizard.

選項Options

  • 不要再顯示此頁面。Do not show this page again. - 按一下此核取方塊,之後就不會再顯示 [簡介] 頁面。- Click the check box to stop the Introduction page from being displayed in the future.

  • 下一步 - 繼續進行 [匯入設定] 頁面。Next - Proceeds to the Import Settings page.

  • 取消 - 取消作業並關閉精靈。Cancel - Cancels the operation and closes the wizard.

匯入設定頁面Import Settings Page

您可以使用此頁面來指定要匯入之 .bacpac 檔案的位置。Use this page to specify the location of the .bacpac file to import.

  • 從本機磁碟匯入 - 按一下 [瀏覽...] 巡覽本機電腦,或在提供的空間中指定路徑。Import from local disk - Click Browse... to navigate the local computer, or specify the path in the space provided. 路徑名稱必須包含檔案名稱和 .bacpac 副檔名。The path name must include a file name and the .bacpac extension.

  • 從 Azure 匯入 - 從 Microsoft Azure 容器匯入 BACPAC 檔案。Import from Azure - Imports a BACPAC file from a Microsoft Azure container. 您必須連線到 Microsoft Azure 容器,才能驗證此選項。You must connect to a Microsoft Azure container to validate this option. 請注意,[從 Azure 匯入] 選項也會要求您指定暫存檔案的本機目錄。Note that the Import from Azure option also requires that you specify a local directory for the temporary file. 暫存檔將建立在指定的位置,而且作業完成之後,將保留在該位置。The temporary file will be created at the specified location and will remain there after the operation completes.

    瀏覽 Azure 時,您可以在單一帳戶中的容器之間切換。When browsing Azure, you will be able to switch between containers within a single account. 您必須指定單一 .bacpac 檔案,才能繼續進行匯入作業。You must specify a single .bacpac file to continue the import operation. 您可以依照 [名稱] 、[大小] 或 [修改日期] 排序資料行。You can sort columns by Name, Size, or Date Modified.

    若要繼續進行,請指定要匯入的 .bacpac 檔案,然後按一下 [開啟]To continue, specify the .bacpac file to import, and then click Open.

資料庫設定頁面Database Settings Page

您可以使用此頁面指定要建立之資料庫的詳細資料。Use this page to specify details for the database that will be created.

若為 SQL Server 的本機執行個體:For a local instance of SQL Server:

  • 新資料庫名稱 - 針對匯入的資料庫提供名稱。New database name - Provide a name for the imported database.

  • 資料檔案路徑 - 提供資料檔案的本機目錄。Data file path - Provide a local directory for data files. 按一下 [瀏覽...] 巡覽本機電腦,或在提供的空間中指定路徑。Click Browse... to navigate the local computer, or specify the path in the space provided.

  • 記錄檔路徑 - 提供記錄檔的本機目錄。Log file path - Provide a local directory for log files. 按一下 [瀏覽...] 巡覽本機電腦,或在提供的空間中指定路徑。Click Browse... to navigate the local computer, or specify the path in the space provided.

若要繼續進行,請按 [下一步]To continue, click Next.

針對 Azure SQL Database:For an Azure SQL Database:

驗證頁面Validation Page

您可以使用此頁面檢閱造成此作業無法執行的任何問題。Use this page to review any issues that block the operation. 若要繼續進行,請解決封鎖問題,然後按一下 [重新執行驗證] 確定驗證成功。To continue, resolve blocking issues and then click Re-run Validation to ensure that validation is successful.

若要繼續進行,請按 [下一步]To continue, click Next.

摘要頁面Summary Page

您可以使用此頁面來檢閱作業的指定來源和目標設定。Use this page to review the specified source and target settings for the operation. 若要使用指定的設定來完成匯入作業,請按一下 [完成]To complete the import operation using the specified settings, click Finish. 若要取消匯入作業並結束精靈,請按一下 [取消]To cancel the import operation and exit the wizard, click Cancel.

進度頁面Progress Page

此頁面會顯示進度列,指出作業的狀態。This page displays a progress bar that indicates the status of the operation. 若要檢視詳細狀態,請按一下 [檢視詳細資料] 選項。To view detailed status, click the View details option.

若要繼續進行,請按 [下一步]To continue, click Next.

結果頁面Results Page

此頁面會報告匯入和建立資料庫作業成功或失敗,並顯示每個動作成功或失敗。This page reports the success or failure of the import and create database operations, showing the success or failure of each action. 發生錯誤的所有動作在 [結果] 資料行中都會有一個連結。Any action that encountered an error will have a link in the Result column. 按一下連結,即可檢視該動作的錯誤報告。Click the link to view a report of the error for that action.

按一下 [關閉] ,關閉精靈。Click Close to close the wizard.

另請參閱See Also

匯入 BACPAC 檔案以建立新的 Azure SQL DatabaseImport a BACPAC file to create a new Azure SQL database
資料層應用程式 Data-tier Applications
匯出資料層應用程式Export a Data-tier Application