Art of the DBA Rotating Header Image

Dynamically Extracting .dacpacs With #Powershell

As DBAs, backing up our databases isn’t quite enough.  We also want to back up our schemas for additional protection.  And while we could go through some complex scripting to actual write out all those schema objects, Microsoft has given us a handy tool with SQL Server Data Tools and Visual Studio to capture these schemas.

I’m talking about sqlpackage.  I hadn’t used this before I got to my current job, but it really is a snap.  Using sqlpackage (in conjunction with managing your database through a Visual Studio project), you can easily deploy or script out database code changes.  You can also extract your schema with a few simple commands, like so:

sqlpackage.exe /action:extract /targetservername:localhost /targetdatabasename:dummy /targetfile:someawesome.dacpac

So this is great and all, but when you’ve got dozens or even hundreds of databases, you need a quick and efficient way to run this command.  Since this is a command line operation, Powershell becomes the natural way to do this.  Here’s a simple script that will allow you to extract all the user database schemas from a server:

#extract all your database schemas as dacpacs
$server = 'PICARD'

$dbs = Invoke-Sqlcmd -ServerInstance $server -Database tempdb -Query 'SELECT name FROM sys.databases WHERE database_id >4'

foreach($db in $dbs.name){
$cmd = "& 'C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe' /action:Extract /targetfile:'C:\dacpacs\$db.dacpac' /SourceServerName:$server /SourceDatabaseName:$db"

Invoke-Expression $cmd
}

Let’s look at two things in play here.  The first is the use of the ‘&’, which is the call operator.  This takes a string expression you create and then executes it.  In this case, we build out the expression with that operator.  Then we use item number two, Invoke-Expression, to run the expression we built.  The rest is pretty simple.

This is a quick and dirty version of it, but we could get a lot fancier.  In fact, Gianluca Sartori(@spaghettidba) has a very nice blog post where he provides a function to do this.  My own function (which looks pretty similar) goes a little something like this:

function Export-SQLDacPacs{
    param([string[]] $Instances = 'localhost',
          [string] $outputdirectory=([Environment]::GetFolderPath("MyDocuments"))
)

#get the sqlpackage executable
$sqlpackage = (get-childitem C:\ -Recurse | Where-Object {$_.name -eq 'sqlpackage.exe'} |Sort-Object LastWriteTime | Select-Object -First 1).FullName

#declare a select query for databases
$dbsql = @"
SELECT name FROM sys.databases
where database_id >4 and state_desc = 'ONLINE'
"@

#loop through each instance
foreach($instance in $Instances){
    #set processing variables
    $dbs = Invoke-Sqlcmd -ServerInstance $instance -Database tempdb -Query $dbsql
    $datestring =  (Get-Date -Format 'yyyyMMddHHmm')
    $iname = $instance.Replace('\','_')

    #extract each db
    foreach($db in $dbs.name){
        $outfile = Join-Path $outputdirectory -ChildPath "$iname-$db-$datestring.dacpac"
        $cmd = "& '$sqlpackage' /action:Extract /targetfile:'$outfile' /SourceServerName:$instance /SourceDatabaseName:$db"
        Invoke-Expression $cmd
        }
    }
}

Export-SQLDacPacs -instances 'PICARD' 

What’s cool is with this is a function, we can now extend it and do some really cool things.  Remember that CMS lookup we used last week?  Let’s use it here:

$CMS=’PICARD’
$servers=@((dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$CMS").Name)

$servers+=$cms
Export-SQLDacPAcs -Instances $servers

And now I have a script to extract all my user database schemas from all the instances in my enterprise.  How cool is that?  Now I can put this into an agent job or a Windows scheduled task and have a regular schema extract, giving me an extra layer of protection for my systems.

3 Comments

  1. Rob Volk says:

    When next we meet, I owe you many, many, MANY drinks of your choice.

  2. […] Dynamically Extracting .dacpacs With #Powershell – Mike Fal (Blog|Twitter) […]

  3. David Howell says:

    Hey this is great, thanks for sharing. I love that your CMS is called Picard.

    With this part:
    get-childitem C:\ -Recurse

    I don’t think it’s a great idea to search the entire C drive to find the exe. It could be slow on disks with lots of files, and you may have access restrictions that would cause this to error on certain installations. Perhaps narrow it down to the possible locations using %PROGRAM FILES%\Microsoft SQL Server or C:\Program Files (x86)\Microsoft SQL Server

    Even then, you might have multiple versions installed, and you really want to be using the one that matches the source & target version of SQL Server. You could parameterize that part.

    I’m looking at doing this in powershell by importing the .NET dacpac DLL, details here: http://www.sqlskills.com/blogs/bobb/using-powershell-with-dac-3-0/

Leave a Reply to Rob Volk Cancel reply

Your email address will not be published. Required fields are marked *