How to: Extract a DAC From a Database
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
Create a file ExtractDAC.ps1.
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 .
Add code to specify the database. This example specifies a database named MyDB:
## Specify the database to extract to a DAC. $dbname = "MyDB"
Specify the metadata for the DAC. This example specifies the DAC name, version, and description:
## Specify the DAC metadata. $applicationname = "MyApplication" $version = "220.127.116.11" $description = "This DAC defines the database used by my application."
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"
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)
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
In Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Extract Data-tier Application.
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.