Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

How to: Extract a DAC From a Database

SQL Server 2008 R2

Use either the Extract a 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.

You can extract a DAC from databases residing on instances of SQL Azure, or SQL Server 2005 Service Pack 4 (SP4) or later.

The Extract a Data-tier Application Wizard does not register the DAC definition in the current instance of the Database Engine. For more information about registering a DAC, see How to: Register a Database As a DAC.

Building and Running a Script

  1. Create a file ExtractDAC.ps1.

  2. Add code to create a SMO Server object and set it to the instance containing the database from which you want to extract a DAC. This example sets a Server object to the default instance on the local computer:

    ## Set a SMO Server object to the default instance on the local computer.
    CD SQLSERVER:\SQL\localhost\DEFAULT
    $srv = get-item .
    
  3. Add code to specify the database. This example specifies a database named MyDB:

    ## Specify the database to extract to a DAC.
    $dbname = "MyDB"
    
  4. Specify the metadata for the DAC. This example specifies the DAC name, version, and description:

    ## Specify the DAC metadata.
    $applicationname = "MyApplication"
    $version = "1.0.0.0"
    $description = "This DAC defines the database used by my application."
    
  5. Specify the path and file name for the extracted DAC package file.

    ## Specify the location and name for the extracted DAC package.
    $dacpacPath = "C:\MyDACs\MyApplication.dacpac"
    
  6. Add code to run the Extract method with the information specified above.

    ## Extract the DAC.
    $extractionunit = New-Object Microsoft.SqlServer.Management.Dac.DacExtractionUnit($srv, $dbname, $applicationname, $version)
    $extractionunit.Description = $description
    $extractionunit.Extract($dacpacPath)
    
  7. Run ExtractDAC.ps1 from either a PowerShell session in which you have loaded the SQL Server PowerShell snapins, or by using the sqlps command prompt utility.

To open the Extract a Data-tier Application Wizard

  1. In Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Extract Data-tier Application.

  2. Follow the steps in the wizard to create a DAC package based on the database you select. For more information, see Extract Data-tier Application Wizard F1 Help.

Updated content

Added section on using PowerShell.

Community Additions

ADD
Show:
© 2015 Microsoft