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.

Advertisements

Posted on February 13, 2013, in PowerShell, SQL Server and tagged , , , , , . Bookmark the permalink. 9 Comments.

  1. I posted something not too dissimilar earlier this year, and am so much more impressed with your offering.

  2. 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..

  3. 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

  4. 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

  5. I created this solution to automate the import into Visual Studio solution

    https://github.com/rwforest/dacpac2sln

  1. Pingback: Six of the Best: September 2013 |

  2. Pingback: Extracting DACPAC From SQL Server Database With PowerShell | Andrey Zavadskiy – SQL and .NET developer, trainer, speaker

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: