Extracting DACPACs from all databases with Powershell

If you are adopting Sql Server Data Tools as your election tool to maintain database projects under source control and achieve an ALM solution, at some stage you will probably want to import all your databases in SSDT.

Yes, it can be done by hand, one at a time, using either the “import live database” or “schema compare” features, but what I have found to be more convenient is the “import dacpac” feature.

Basically, you can extract a dacpac from a live database and then import it in SSDT, entering some options in the import dialog.

The main reason why I prefer this method is the reduced amount of manual steps involved. Moreover, the dacpac extraction process can be fully automated using sqlpackage.exe.

Recently I had to import a lot of databases in SSDT and found that sqlpackage can be used in a PowerShell script to automate the process even further:

#
# Extract DACPACs from all databases
#
# Author: Gianluca Sartori - @spaghettidba
# Date:   2013/02/13
# Purpose:
# Loop through all user databases and extract
# a DACPAC file in the working directory
#
#

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

cls

try {
    if((Get-PSSnapin -Name SQlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null){
        Add-PSSnapin SQlServerCmdletSnapin100
    }
}
catch {
    Write-Error "This script requires the SQLServerCmdletSnapIn100 snapin"
    exit
}

#
# Gather working directory (script path)
#
$script_path = Split-Path -Parent $MyInvocation.MyCommand.Definition

$sql = "
    SELECT name
    FROM sys.databases
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb','distribution')
"

$data = Invoke-sqlcmd -Query $sql -ServerInstance $ServerName -Database master

$data | ForEach-Object {

    $DatabaseName = $_.name

    #
    # Run sqlpackage
    #
    &"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" `
        /Action:extract `
        /SourceServerName:$ServerName `
        /SourceDatabaseName:$DatabaseName `
        /TargetFile:$script_path\DACPACs\$DatabaseName.dacpac `
        /p:ExtractReferencedServerScopedElements=False `
        /p:IgnorePermissions=False

}

It’s a very simple script indeed, but it saved me a lot of time and I wanted to share it with you.

Unfortunately, there is no way to automate the import process in SSDT, but looks like Microsoft is actually looking into making this feature availabe in a future version.

Archived WordPress comments (7)

Historical comments from the original site; this archive is read-only.

Richard Lee September 11, 2013 at 16:06
I posted something not too dissimilar earlier this year, and am so much more impressed with your offering.
1. spaghettidba September 11, 2013 at 16:49
  Thanks. Glad you liked it.
Rdkn February 19, 2016 at 04:00
This is great! I slightly tweaked it to add timestamp to a filename.

Have you tried scheduling this on regular basis through SQL Agent though? I tried, but I'm getting syntax errors all over. Runs fine in Powershell ISE though..
Sunil October 18, 2016 at 20:52
How do I avoid hardcoding the path to sqlpackage => C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe
Some of the machines have earlier version of Sql Server and the path varies C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe
1. Sunil October 18, 2016 at 20:53
  test
DorababuMeka April 26, 2017 at 11:33
Hello I am having a sqlcmd variable defined which is used while deploying the dacpac but after extracting I am not getting the same result is there a way to exclude

Sample image when verifying differences

https://social.msdn.microsoft.com/Forums/getfile/1054804
firstmovechess July 7, 2017 at 06:56
I created this solution to automate the import into Visual Studio solution

https://github.com/rwforest/dacpac2sln

See also