從資料庫中擷取 DACExtract a DAC From a Database

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

您可以使用 [擷取資料層應用程式精靈] 或 Windows PowerShell 指令碼,從現有的 SQL Server 資料庫中擷取資料層應用程式 (DAC) 封裝。Use either the Extract Data-tier Application Wizard or a Windows PowerShell script to extract a data-tier application (DAC) package from an existing SQL Server database. 此擷取程序會建立 DAC 封裝檔案,其中包含資料庫物件及其相關執行個體層級元素的定義。The extraction process creates a DAC package file that contains definitions of the database objects and their related instance-level elements. 例如,DAC 封裝檔案會包含資料庫資料表、預存程序、檢視表、使用者以及對應至資料庫使用者的登入。For example, a DAC package file contains the database tables, stored procedures, views, and users, along with the logins that map to the database users.

開始之前Before you begin

您可以從位於 SQL DatabaseSQL DatabaseSQL Server 2000 (8.x)SQL Server 2000 (8.x) Service Pack 4 或更新版本之執行個體的資料庫中擷取 DAC。You can extract a DAC from databases residing on instances of SQL DatabaseSQL Database, or SQL Server 2000 (8.x)SQL Server 2000 (8.x) Service Pack 4 or later. 如果您針對從 DAC 部署的資料庫來執行擷取程序,則只會擷取資料庫中物件的定義。If the extraction process is run against a database that was deployed from a DAC, only the definitions of the objects in the database are extracted. 此程序不會參考 msdb 中註冊的 DAC (中的 master SQL DatabaseSQL Database)。The process does not reference the DAC registered in msdb (master in SQL DatabaseSQL Database). 擷取程序不會在目前的 Database Engine 執行個體中註冊 DAC 定義。The extraction process does not register the DAC definition in the current instance of the Database Engine. 如需有關註冊 DAC 的詳細資訊,請參閱< Register a Database As a DAC>。For more information about registering a DAC, see Register a Database As a DAC.

限制事項Limitations and restrictions

DAC 只能從 SQL DatabaseSQL DatabaseSQL Server 2005 (9.x)SQL Server 2005 (9.x) Service Pack 4 (SP4) 或更新版本的資料庫中進行擷取。A DAC can only be extracted from a database in SQL DatabaseSQL Database, or SQL Server 2005 (9.x)SQL Server 2005 (9.x) Service Pack 4 (SP4) or later. 如果 DAC 或包含的使用者中不支援資料庫中的物件,則無法擷取 DAC。You cannot extract a DAC if the database has objects that are not supported in a DAC, or contained users. 如需有關 DAC 中支援之物件類型的詳細資訊,請參閱< DAC Support For SQL Server Objects and Versions>。For more information about the types of objects supported in a DAC, see DAC Support For SQL Server Objects and Versions.

PermissionsPermissions

擷取 DAC 至少需要 ALTER ANY LOGIN 和資料庫範圍 VIEW DEFINITION 權限,以及 sys.sql_expression_dependencies的 SELECT 權限。Extracting a DAC 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 固定資料庫角色的成員。Extracting 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 extracted. sysadmin 固定伺服器角色的成員或是內建 SQL Server 系統管理員帳戶 sa 也可以擷取 DAC。Members of the sysadmin fixed server role or the built-in SQL Server system administrator account named sa can also extract a DAC.

使用擷取資料層應用程式精靈Using the Extract Data-tier Application Wizard

使用精靈擷取 DACTo Extract a DAC Using a Wizard

  1. [物件總管] 中,展開含有待擷取 DAC 之資料庫的執行個體的節點。In Object Explorer, expand the node for the instance containing the database from which the DAC is to be extracted.

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

  3. 以滑鼠右鍵按一下待擷取 DAC 之資料庫的節點,並指向 [工作],然後選取 [擷取資料層應用程式...]Right-click the node for the database from which the DAC is to be extracted, point to Tasks, and then select Extract Data-tier Application...

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

    1. 簡介頁面Introduction Page

    2. 選取資料頁面Select Data Page

    3. 設定屬性頁面Set Properties Page

    4. 驗證與摘要頁面Validation and Summary Page

    5. 建立封裝頁面Build Package Page

精靈簡介頁面Wizard introduction page

此頁面描述擷取資料層應用程式的步驟。This page describes the steps for extracting a data-tier application.

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

下一步 > - 繼續進行至 [選擇方法] 頁面。Next > - Proceeds to the Choose Method page.

取消 :結束精靈,不從資料庫中擷取資料層應用程式。Cancel - Ends the wizard without extracting a data-tier application from the database.

[擷取精靈][Extract Wizard]

Select data pageSelect data page

選取您想要包含在資料層應用程式 (DAC) 封裝檔案中的參考資料。Select the reference data that you want to include in your data-tier application (DAC) package file. 在您的 DAC 封裝檔案中包含資料是選擇性的。Including data in your DAC package is optional. DAC 封裝已包含所有受支援資料庫物件和資料庫相關執行個體物件的結構描述。The DAC package will already include the schema of all supported database objects and instance objects related to your database.

您可以在 DAC 封裝檔案中最多包含 10 MB 的參考資料。You can include up to 10 MB of reference data in your DAC package file. 不過,若要在 DAC 包含資料表,資料表不可以包含二進位大型物件 (BLOB) 資料類型,例如 imagevarchar(max)However, for tables to be included in the DAC, they may not contain binary large object (BLOB) data types such as image or varchar(max). 若要擷取大量資料以傳送至另一個資料庫,請使用 SQL Server Integration Services、大量複製公用程式或許多其他資料移轉技術。To extract larger amounts of data for transferring to another database, use SQL Server Integration Services, the bulk copy utility, or one of many other data migration techniques.

資料庫資料表 :選取資料庫資料表旁邊的核取方塊,這些資料庫資料表包含您要併入 DAC 封裝中的資料。Database table - Select the check box next to the database tables which contain the data that you want to include in your DAC package. 您最多可以選取十個不超過 10,000 資料列的資料表。You can select up to ten tables that have 10,000 rows or less.

[擷取精靈][Extract Wizard]

Set properties pageSet properties page

您可以使用此精靈的這個頁面來描述資料層應用程式 (DAC)。Use this page of the wizard to describe the data-tier application (DAC). 這些屬性會用來識別 DAC,並協助您區別其他項目。These properties are used to identify the DAC and help distinguish it from others.

名稱 :此名稱會識別 DAC。Name - This name identifies the DAC. 它可能與 DAC 封裝檔案的名稱不同,而且應該會描述您的應用程式。It can be different than the name of the DAC package file and should describe your application. 例如,如果此資料庫用於財務應用程式,您可能會想要命名為 DAC Finance。For example, if the database is used for a finance application, you may wish to name the DAC Finance.

版本 (使用 xx.xx.xx.xx,其中 x 是數字) :識別 DAC 版本的數值。Version (use xx.xx.xx.xx, where x is a number) - A numeric value that identifies the version of the DAC. DAC 版本會用於 Visual Studio 中,以便識別開發人員正在處理的 DAC 版本。The DAC version is used in Visual Studio to identify the version of the DAC that developers are working on. 部署 DAC 時,此版本會儲存在 msdb 資料庫中,而且您之後可以在 SQL Server Management StudioSQL Server Management Studio 的 [資料層應用程式] 節點底下檢視此版本。When deploying a DAC, the version is stored in the msdb database and can later be viewed under the Data-tier Applications node in SQL Server Management StudioSQL Server Management Studio.

描述:選擇性。Description: - Optional. 描述此 DAC。Describes the DAC. 部署 DAC 時,此描述會儲存在 msdb 資料庫中,而且您之後可以在 Management StudioManagement Studio 的 [資料層應用程式] 節點底下檢視此描述。When deploying a DAC, the description is stored in the msdb database and can later be viewed under the Data-tier Applications node in Management StudioManagement Studio.

儲存至 DAC 封裝檔案 (檔案名稱包含 .dacpac 副檔名):將 DAC 儲存至副檔名為 .dacpac 的 DAC 封裝檔案。Save to DAC package file (include .dacpac extension with file name): - Saves the DAC to a DAC package file, with a .dacpac extension. 按一下 [瀏覽] 按鈕,即可指定檔案的名稱和位置。Click the Browse button to specify a name and location for the file.

覆寫現有檔案 :如果已經有同名的 DAC 封裝檔案,請選取此核取方塊來取代該檔案。Overwrite existing file - Select this check box to replace the DAC package file if one already exists with the same name.

Validation and summary pageValidation and summary page

在這個頁面上,此精靈會驗證資料層應用程式 (DAC) 是否支援所有資料庫物件。On this page, the wizard validates that all database objects are supported by a data-tier application (DAC). 此外,它也會檢查資料庫物件之間的相依性,以便判斷可成功包含在 DAC 中的物件集合。It also checks dependencies across database objects to determine the set of objects that can be successfully included in the DAC. 之後,它會顯示驗證報表並摘要列出您在這個精靈中所選取的選項。After that, it displays the validation report and summarizes the options that you have selected in this wizard. 若要變更選項,請按 [上一步]To change an option, click Previous. 若要開始擷取 DAC,請按 [下一步]To begin extracting a DAC, click Next.

注意! 如果 DAC 不支援一個或多個物件,[下一步] 按鈕就會停用,而且擷取程序便無法繼續。NOTE! If one or more objects are not supported by a DAC, then the Next button is disabled and the extraction process may not continue. 在這種情況下,建議您移除不支援的物件,然後再次執行此精靈。In such cases, it is recommended to remove the non-supported objects and then run this wizard again.

摘要:所選取的選項摘要會列在 [DAC 屬性] 底下。Summary - A summary of the options you have selected are listed under DAC properties. 驗證的結果則列在 [DAC 物件] 底下。The results of the validation are listed under DAC objects. 驗證的結果有三種類型:There are three types of results from the validation:

  • 物件成功包含在 DAC 中:表示這些物件及其相依性受到支援,而且可以成功包含在 DAC 中。Objects included in DAC successfully: these objects and their dependencies are supported, and can be included in the DAC successfully.

  • 物件包含在 DAC 中,但是出現警告:表示雖然這些物件受到支援,不過卻相依於 DAC 不支援的其他物件。Objects included in DAC with warnings: these objects are supported, but depend on other objects that are not supported in a DAC.

  • 物件未包含在 DAC 中:表示這些物件不受到支援,而且必須從資料庫中移除它們,然後才能成功擷取 DAC。Objects not included in DAC: these objects are not supported and must be removed from the database before successfully extracting a DAC.

驗證程序會檢查多個相依性層級。The validation process checks multiple levels of dependencies. 例如,如果某個預存程序相依於使用不支援之 CLR 資料類型的資料表,此預存程序就會列在 [物件包含在 DAC 中,但是出現警告] 底下。For example, if a stored procedure depends on a table that uses the unsupported CLR data type, the stored procedure will be listed under Objects included in DAC with warnings.

如果 DAC 不支援一個或多個物件, [下一步] 按鈕就會停用,而且擷取程序將無法繼續。If one or more objects are not supported by a DAC, then the Next button is disabled and the extraction process will not continue. 在這種情況下,建議您移除不支援的物件,然後再次執行此精靈。In such cases, it is recommended to remove the objects that are not supported and then run this wizard again.

儲存報表:可讓您儲存以 HTML 為基礎的檔案,其中列出摘要之 [DAC 物件] 節點底下的所有物件。Save Report - Enables you to save an HTML-based file that lists all of the objects under the DAC Objects node in the summary. 當 DAC 不支援部分資料庫物件時,這份報表可能會很有用。This report can be useful when some of your database objects are not supported in a DAC. 您可以先使用此報表來變更或移除不支援的物件,然後再次嘗試擷取 DAC。Use the report to change or remove objects that are not supported, before trying to extract the DAC again.

Build package pageBuild package page

您可以使用這個頁面來監視此精靈擷取資料層應用程式 (DAC) 的進度。Use this page to monitor the progress of the wizard as it extracts the data-tier application (DAC).

動作:在 [建立並儲存 DAC 封裝檔案] 動作期間,此精靈會從 SQL Server 資料庫中擷取 DAC。Action - During the Create and save DAC package file action, the wizard extracts a DAC from your SQL Server database. 然後,它會在記憶體中建立 DAC 封裝並儲存至您所指定的位置。Then, a DAC package is created in memory and saved to the location you specified. 若要查看對應步驟的結果,請按一下 [結果] 欄中的連結。Click on the links in the Result column to see the outcome of the corresponding step.

儲存報表 :按一下即可將精靈進度的結果儲存至檔案。Save Report - Click to save the results of the wizard's progress to a file.

完成 :在處理完成之後或是發生錯誤時,按一下即可關閉精靈。Finish - Click to close the wizard after processing has completed, or if an error occurs.

使用 PowerShell 擷取 DACExtract a DAC using PowerShell

在 PowerShell 指令碼中使用 Extract() 方法,從資料庫中擷取 DACTo extract a DAC from a database using the Extract() method in a PowerShell script

  1. 建立 SMO Server 物件,並將它設為含有待擷取 DAC 之資料庫的執行個體。Create a SMO Server object and set it to the instance that contains the database from which the DAC is to be extracted.

  2. 加入可指定資料庫名稱的變數。Add a variable that specifies the name of the database.

  3. 指定 DAC 的中繼資料,例如 DAC 名稱、版本及描述。Specify the metadata for the DAC, such as the DAC name, version, and description.

  4. 為已擷取的 DAC 封裝檔案指定路徑和檔案名稱。Specify the path and file name for the extracted DAC package file.

  5. 使用上述指定的資訊執行擷取方法。Run the Extract method with the information specified above.

範例 (PowerShell)Example (PowerShell)

下列範例會從 MyDB 資料庫中擷取名為 MyApplication 的 DAC。The following example extracts a DAC named MyApplication from a database named MyDB.

## Set a SMO Server object to the default instance on the local computer.  
CD SQLSERVER:\SQL\localhost\DEFAULT  
$srv = get-item .  
  
## Specify the database to extract to a DAC.  
$dbname = "MyDB"  
  
## Specify the DAC metadata.  
$applicationname = "MyApplication"  
$version = "1.0.0.0"  
$description = "This DAC defines the database used by my application."  
  
## Specify the location and name for the extracted DAC package.  
$dacpacPath = "C:\MyDACs\MyApplication.dacpac"  
  
## Extract the DAC.  
$extractionunit = New-Object Microsoft.SqlServer.Management.Dac.DacExtractionUnit($srv, $dbname, $applicationname, $version)  
$extractionunit.Description = $description  
$extractionunit.Extract($dacpacPath)  

另請參閱See also

資料層應用程式Data-tier Applications