使用 DAC 來部署資料庫Deploy a Database By Using a DAC

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

使用 [將資料庫部署到 SQL Azure] 精靈,在 Database EngineDatabase Engine 執行個體與 Azure SQL DatabaseAzure SQL Database 伺服器之間部署資料庫,或在兩個 Azure SQL DatabaseAzure SQL Database伺服器之間部署資料庫。Use the Deploy a Database to SQL Azure Wizard to deploy a database between an instance of the Database EngineDatabase Engine and a Azure SQL DatabaseAzure SQL Database server, or between two Azure SQL DatabaseAzure SQL Databaseservers.

開始之前Before You Begin

精靈會使用資料層應用程式 (DAC) BACPAC 封存檔案,來部署資料和資料庫物件的定義。The wizard uses a Data-tier Application (DAC) BACPAC archive file to deploy both the data and the definitions of database objects. 它會從來源資料庫執行 DAC 匯出作業並對目的地資料庫執行 DAC 匯入。It performs a DAC export operation from the source database, and a DAC import to the destination.

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

根據預設,部署期間建立的資料庫將會擁有 CREATE DATABASE 陳述式中的預設值。By default, the database created during the deployment will have the default settings from the CREATE DATABASE statement. 例外是資料庫定序和相容性層級會設定為來源資料庫中的值。The exception is that the database collation and compatibility level are set to the values from the source database.

資料庫選項 (例如 TRUSTWORTHY、DB_CHAINING 和 HONOR_BROKER_PRIORITY) 無法在部署過程中調整。Database options, such as TRUSTWORTHY, DB_CHAINING and HONOR_BROKER_PRIORITY, cannot be adjusted as part of the deployment process. 實體屬性 (如檔案群組數目或檔案數目和大小) 無法在部署過程中更改。Physical properties, such as the number of filegroups, or the numbers and sizes of files cannot be altered as part of the deployment process. 部署完成之後,您可以使用 ALTER DATABASE 陳述式、 SQL Server Management StudioSQL Server Management StudioSQL ServerSQL Server PowerShell 來修改資料庫。After the deployment completes, you can use the ALTER DATABASE statement, SQL Server Management StudioSQL Server Management Studio, or SQL ServerSQL Server PowerShell to tailor the database.

限制事項Limitations and Restrictions

[部署資料庫] 精靈支援在下列項目上部署資料庫:The Deploy Database wizard supports deploying a database:

  • Database EngineDatabase Engine 執行個體至 Azure SQL DatabaseAzure SQL DatabaseFrom an instance of the Database EngineDatabase Engine to Azure SQL DatabaseAzure SQL Database.

  • Azure SQL DatabaseAzure SQL DatabaseDatabase EngineDatabase Engine執行個體。From Azure SQL DatabaseAzure SQL Database to an instance of the Database EngineDatabase Engine.

  • 兩個 Azure SQL DatabaseAzure SQL Database 伺服器之間。Between two Azure SQL DatabaseAzure SQL Database servers.

精靈不支援在兩個 Database EngineDatabase Engine執行個體之間部署資料庫。The wizard does not support deploying databases between two instances of the Database EngineDatabase Engine.

Database EngineDatabase Engine 執行個體必須執行 SQL Server 2005 (9.x)SQL Server 2005 (9.x) Service Pack 4 (SP4) 或更新版本,才能使用精靈。An instance of the Database EngineDatabase Engine must be running SQL Server 2005 (9.x)SQL Server 2005 (9.x) Service Pack 4 (SP4) or later to work with the wizard. 如果 Database EngineDatabase Engine 執行個體上的資料庫物件不受 Azure SQL DatabaseAzure SQL Database支援,則無法使用此精靈將資料庫部署到 Azure SQL DatabaseAzure SQL DatabaseIf a database on an instance of the Database EngineDatabase Engine contains objects not supported on Azure SQL DatabaseAzure SQL Database, you cannot use the wizard to deploy the database to Azure SQL DatabaseAzure SQL Database. 如果 Azure SQL DatabaseAzure SQL Database 上的資料庫物件不受 SQL ServerSQL Server支援,則無法使用此精靈將資料庫部署到 SQL ServerSQL Server執行個體。If a database on Azure SQL DatabaseAzure SQL Database contains objects not supported by SQL ServerSQL Server, you cannot use the wizard to deploy the database to instances of SQL ServerSQL Server.

安全性Security

為了提高安全性,SQL Server 驗證登入會儲存在 DAC BACPAC 檔案中,而且沒有密碼。To improve security, SQL Server Authentication logins are stored in a DAC BACPAC file without a password. 當您匯入 BACPAC 之後,此登入會建立為停用的登入,而且會產生密碼。When the BACPAC is imported, the login is created as a disabled login with a generated password. 若要啟用登入,請使用具有 ALTER ANY LOGIN 權限的登入進行登入,並使用 ALTER LOGIN 來啟用登入,然後指派可以傳達給使用者的新密碼。To enable the logins, log 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

精靈需要來源資料庫的 DAC 匯出權限。The wizard requires DAC export permissions on the source database. 登入至少需要 ALTER ANY LOGIN 和資料庫範圍 VIEW DEFINITION 權限,以及 sys.sql_expression_dependencies的 SELECT 權限。The login requires at least ALTER ANY LOGIN and database scope VIEW DEFINITION permissions, as well as SELECT permissions on sys.sql_expression_dependencies. 匯出 DAC 可以透過 securityadmin 固定伺服器角色的成員來完成,這個角色的成員也是匯出 DAC 之來源資料庫中 database_owner 固定資料庫角色的成員。Exporting a DAC can be done by members of the securityadmin fixed server role who are also members of the database_owner fixed database role in the database from which the DAC is exported. 系統管理員固定伺服器角色的成員或內建 SQL Server 系統管理員帳戶 sa 也可以匯出 DAC。Members of the sysadmin fixed server role or the built-in SQL Server system administrator account named sa can also export a DAC.

精靈需要目的地執行個體或伺服器的 DAC 匯入權限。The wizard requires DAC import permissions on the destination instance or server. 登入必須是 系統管理員 (sysadmin)伺服器管理員 (serveradmin) 固定伺服器角色的成員,或是具有 dbcreator 固定伺服器角色及擁有 ALTER ANY LOGIN 權限。The login must be a member of the sysadmin or serveradmin fixed server roles, or 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 Deploy Database Wizard

若要使用部署資料庫精靈移轉資料庫To migrate a database using the Deploy Database Wizard

  1. 連接至您要部署的資料庫位置。Connect to the location of the database you want to deploy. 您可以指定 Database EngineDatabase Engine 執行個體或 Azure SQL DatabaseAzure SQL Database 伺服器。You can specify either an instance of Database EngineDatabase Engine or a Azure SQL DatabaseAzure SQL Database server.

  2. [物件總管] 中,展開含有資料庫的執行個體的節點。In Object Explorer, expand the node for the instance that has the database.

  3. 展開 [資料庫] 節點。Expand the Databases node.

  4. 以滑鼠右鍵按一下您要部署的資料庫,選取 [工作] ,然後選取 [將資料庫部署到 SQL Azure...] Right click the database you want to deploy, select Tasks, and then select Deploy Database to SQL Azure...

  5. 完成精靈對話方塊:Complete the Wizard dialogs:

簡介頁面Introduction Page

此頁面描述 [部署資料庫] 精靈的步驟。This page describes the steps for the Deploy Database 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 Deployment Settings page.

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

部署設定頁面Deployment Settings Page

使用此頁面來指定目的地伺服器以及提供新資料庫的詳細資料。Use this page to specify the destination server and to provide details about your new database.

本機主機:Local host:

  • 伺服器連接 - 指定伺服器連接的詳細資料,然後按一下 [連接] 來驗證連接。Server connection - Specify server connection details and then click Connect to verify the connection.

  • 新資料庫名稱 - 指定新資料庫的名稱。New database name - Specify a name for the new database.

SQL DatabaseSQL Database 資料庫設定:SQL DatabaseSQL Database database settings:

  • SQL DatabaseSQL Database 版本 - 從下拉式功能表中選取 SQL DatabaseSQL Database 的版本。SQL DatabaseSQL Database edition - Select the edition of SQL DatabaseSQL Database from the drop-down menu.

  • 資料庫大小上限 - 從下拉式功能表中選取資料庫大小上限。Maximum database size - Select the maximum database size from the drop-down menu.

其他設定:Other settings:

  • 指定暫存檔 (即 BACPAC 封存檔案) 的本機目錄。Specify a local directory for the temporary file, which is the BACPAC archive file. 請注意,檔案將在指定的位置上建立,而且作業完成之後,將保留在該位置。Note that the file will be created at the specified location and will remain there after the operation completes.

摘要頁面Summary Page

您可以使用此頁面來檢閱作業的指定來源和目標設定。Use this page to review the specified source and target settings for the operation. 若要使用指定的設定來完成部署作業,請按一下 [完成]To complete the deploy operation using the specified settings, click Finish. 若要取消部署作業並結束精靈,請按一下 [取消]To cancel the deploy 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.

結果頁面Results Page

此頁面會報告部署作業成功或失敗,並顯示每個動作的結果。This page reports the success or failure of the deploy operation, showing the results 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 Finish to close the Wizard.

使用 .Net Framework 應用程式Using a .Net Framework Application

在 .Net Framework 應用程式中使用 DacStoreExport() 與 Import() 方法,以部署資料庫。To deploy a database using the DacStoreExport() and Import() methods in a .Net Framework application.

若要檢視程式碼範例,請下載 Codeplex上的 DAC 範例應用程式。To view a code example, download the DAC sample application on Codeplex

  1. 建立 SMO Server 物件,並將它設定為包含要部署之資料庫的執行個體或伺服器。Create a SMO Server object and set it to the instance or server that contains the database to be deployed.

  2. 開啟 ServerConnection 物件,並連接到相同的執行個體。Open a ServerConnection object and connect to the same instance.

  3. 使用 Export 類型的 Microsoft.SqlServer.Management.Dac.DacStore 方法,將資料庫匯出至 BACPAC 檔案。Use the Export method of the Microsoft.SqlServer.Management.Dac.DacStore type to export the database to a BACPAC file. 指定要匯出之資料庫的名稱,以及要放置 BACPAC 檔案之資料夾的路徑。Specify the name of the database to be exported, and the path to the folder where the BACPAC file is to be placed.

  4. 建立 SMO Server 物件,並將它設定為目的地執行個體或伺服器。Create a SMO Server object and set it to the destination instance or server.

  5. 開啟 ServerConnection 物件,並連接到相同的執行個體。Open a ServerConnection object and connect to the same instance.

  6. 使用 Import 類型的 Microsoft.SqlServer.Management.Dac.DacStore 方法,匯入 BACPAC。Use the Import method of the Microsoft.SqlServer.Management.Dac.DacStore type to import the BACPAC. 指定匯出所建立的 BACPAC 檔案。Specify the BACPAC file created by the export.

另請參閱See Also

資料層應用程式 Data-tier Applications
匯出資料層應用程式 Export a Data-tier Application
匯入 BACPAC 檔案以建立新的使用者資料庫Import a BACPAC File to Create a New User Database