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:
© 2014 Microsoft