Extracting DACPAC From SQL Server Database With PowerShell

Working with SQL Server Data Tools I’ve faced with a demand to extract data-tier application many times. One of the appropriate solution is to use PowerShell script to extract the database to .dacpac file. Thanks to Gianluca Sartori, he gave a good script to extract DACPAC from all databases (see here).

Based on his script, I’ve did some modification to make it work with SQL Server 2014. I’ve found that I have 3 versions of sqlpackage.exe (110, 120 and 130). So the script looks for the latest one and run it.

Here is the final script:

# Extract data-tier application from specific database

# Input parameters: server and database names
Param(
  [Parameter(Position = 0, Mandatory = $true)]
  [string]$Server,
  [Parameter(Position = 1, Mandatory = $true)]
  [string]$Database
)

# Current directory 
$script_path = Split-Path -Parent $MyInvocation.MyCommand.Definition;

# Find the latest sqlpackage.exe
$last_version = 0;
for($i = 100; $i -le 190; $i += 10)
{
  $file_name = "C:\Program Files (x86)\Microsoft SQL Server\" + $i + "\DAC\bin\sqlpackage.exe";
  If (Test-Path $file_name) {
    $last_version = $i;
  }
}

# Run sqlpackage to extract DACPAC
If ($last_version -gt 0) {
  $msg = "SqlPackage version: " + $last_version;
  Write-Output $msg;
  $file_name = "C:\Program Files (x86)\Microsoft SQL Server\" + $last_version + "\DAC\bin\sqlpackage.exe";

  &$file_name `
    /Action:extract `
    /OverwriteFiles:True `
    /SourceServerName:$Server `
    /SourceDatabaseName:$Database `
    /TargetFile:$script_path\$Database.dacpac `
    /p:ExtractReferencedServerScopedElements=False
}

# Extract data-tier application from specific database

# Input parameters: server and database names

Param(

[Parameter(Position = 0, Mandatory = $true)]

[string]$Server,

[Parameter(Position = 1, Mandatory = $true)]

[string]$Database

)

# Current directory

$script_path = Split-Path -Parent $MyInvocation.MyCommand.Definition;

# Find the latest sqlpackage.exe

$last_version = 0;

for($i = 100; $i -le 190; $i += 10)

{

$file_name = "C:\Program Files (x86)\Microsoft SQL Server\" + $i + "\DAC\bin\sqlpackage.exe";

If (Test-Path $file_name) {

$last_version = $i;

}

# Run sqlpackage to extract DACPAC

If ($last_version -gt 0) {

$msg = "SqlPackage version: " + $last_version;

Write-Output $msg;

$file_name = "C:\Program Files (x86)\Microsoft SQL Server\" + $last_version + "\DAC\bin\sqlpackage.exe";

&$file_name `

/Action:extract `

/OverwriteFiles:True `

/SourceServerName:$Server `

/SourceDatabaseName:$Database `

/TargetFile:$script_path\$Database.dacpac `

/p:ExtractReferencedServerScopedElements=False

}

To execute this script, run SQL Server PowerShell (sqlps) and type one of the following commands (they are identical, but use slight different syntax):

extract_dacpac.ps1 "(local)" Clients
extract_dacpac.ps1 -server "(local)" -database Clients

If everything is OK, you’ll see the progress of execution.

PowerShell script can downloaded as zip file from this link extract_dacpac

One Reply to “Extracting DACPAC From SQL Server Database With PowerShell”

Leave a Reply Cancel reply