Extract a DAC From a Database
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. The extraction process creates a DAC package file that contains definitions of the database objects and their related instance-level elements. 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: Limitations and Restrictions, Permissions
To extract a DAC, using: The Extract Data-tier Application Wizard, PowerShell
Before You Begin
You can extract a DAC from databases residing on instances of SQL Database, or SQL Server 2000 Service Pack 4 or later. 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. The process does not reference the DAC registered in msdb (master in SQL Database). The extraction process does not register the DAC definition in the current instance of the Database Engine. For more information about registering a DAC, see Register a Database As a DAC.
Limitations and Restrictions
A DAC can only be extracted from a database in SQL Database, or SQL Server 2005 Service Pack 4 (SP4) or later. You cannot extract a DAC if the database has objects that are not supported in a DAC, or contained users. For more information about the types of objects supported in a DAC, see DAC Support For SQL Server Objects and Versions.
[Top]
Permissions
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. 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. Members of the sysadmin fixed server role or the built-in SQL Server system administrator account named sa can also extract a DAC.
[Top]
Using the Extract Data-tier Application Wizard
To Extract a DAC Using a Wizard
In Object Explorer, expand the node for the instance containing the database from which the DAC is to be extracted.
Expand the Databases node.
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…
Complete the wizard dialogs:
Introduction Page
Select Data Page
Set Properties Page
Validation and Summary Page
Build Package Page
[Top]
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 Page
Use this page of the wizard to select the reference data that you want to include in your data-tier application (DAC) package file. Including data in your DAC package is optional. The DAC package will already include the schema of all supported database objects and instance objects related to your database.
You can include up to 10 MB of reference data in your DAC package file. 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). 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.
Database table - Select the check box next to the database tables which contain the data that you want to include in your DAC package. You can select up to ten tables that have 10,000 rows or less.
[Extract Wizard]
Set Properties Page
Use this page of the wizard to describe the data-tier application (DAC). These properties are used to identify the DAC and help distinguish it from others.
Name - This name identifies the DAC. It can be different than the name of the DAC package file and should describe your application. For example, if the database is used for a finance application, you may wish to name the DAC Finance.
Version (use xx.xx.xx.xx, where x is a number) - A numeric value that identifies the version of the DAC. The DAC version is used in Visual Studio to identify the version of the DAC that developers are working on. 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 Studio.
Description: - Optional. Describes the DAC. 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 Studio.
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.
Overwrite existing file - Select this check box to replace the DAC package file if one already exists with the same name.
[Extract Wizard]
Validation and Summary Page
On this page, the wizard validates that all database objects are supported by a data-tier application (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. To begin extracting a DAC, click Next.
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.
Summary - A summary of the options you have selected are listed under DAC properties. The results of the validation are listed under DAC objects. There are three types of results from the validation:
Objects included in DAC successfully: these objects and their dependencies are supported, and can be included in the DAC successfully.
Objects included in DAC with warnings: these objects are supported, but depend on other objects that are not supported in a 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. 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.
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.
Save Report - Enables you to save an HTML-based file that lists all of the objects under the DAC Objects node in the summary. This report can be useful when some of your database objects are not supported in a DAC. Use the report to change or remove objects that are not supported, before trying to extract the DAC again.
[Extract Wizard]
Build Package Page
Use this page to monitor the progress of the wizard as it extracts the data-tier application (DAC).
Action - During the Create and save DAC package file action, the wizard extracts a DAC from your SQL Server database. 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.
[Extract Wizard]
Extract a DAC Using PowerShell
To extract a DAC from a database using the Extract() method in a PowerShell script
Create a SMO Server object and set it to the instance that contains the database from which the DAC is to be extracted.
Add a variable that specifies the name of the database.
Specify the metadata for the DAC, such as the DAC name, version, and description.
Specify the path and file name for the extracted DAC package file.
Run the Extract method with the information specified above.
[Top]
Example (PowerShell)
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)
[Top]