Extract DacPacs Using PowerShell Via Sqlpackage Revisited

One of my first posts was a simple PowerShell module that used a bat file to run SqlPackage in cmdline mode to extract dacpacs. I’ve recently made some changes that make it more robust and allow other users to run it. The PowerShell module is just a wrapper around a cmd file that executes sqlpackage.exe to extract the dacpac. Check out my September Six of The Best for another solution provided by Gianluca Sartori.If you’ve never created a module before it’s very simple and I go into the steps below. But first, create a cmd file. The cmd file must contain one line of this for each database you want to extract. The %1 will be the location that we pass in through Powershell.

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /action:Extract /tf:"%1\AdventureWorks_Baseline.dacpac" /SourceConnectionString:"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CubeStaging;Data Source=sqlInstance"

Now we just need to create a PowerShell module. To create a PowerShell Module use this Manifest Module below. If you want to share the module amongst all users of the server then you need to create your module in this location:%windir%\System32\WindowsPowerShell\v1.0\Modules. Save the module with the psd1 extension

#
# Module manifest for module 'DacPac'
#
# Generated by: Richard Lee
#
# Generated on: 03/08/2012
#

@{

# Modules to import as nested modules of the module specified in ModuleToProcess
NestedModules = @('GetDacPac.psm1')

# Version number of this module.
ModuleVersion = '1.0'

# ID used to uniquely identify this module
GUID = 'D3750F0D-BF98-4BB9-9830-264227612CAB'

# Author of this module
Author = 'Richard Lee'

# Company or vendor of this module
CompanyName = 'RP'

# Copyright statement for this module
Copyright = 'RP 2013'

# Description of the functionality provided by this module
Description = 'Module to Create DacPac'

# Minimum version of the Windows PowerShell engine required by this module
PowerShellVersion = ''

# Name of the Windows PowerShell host required by this module
PowerShellHostName = ''

# Minimum version of the Windows PowerShell host required by this module
PowerShellHostVersion = ''

# Minimum version of the .NET Framework required by this module
DotNetFrameworkVersion = ''

# Minimum version of the common language runtime (CLR) required by this module
CLRVersion = ''

# Processor architecture (None, X86, Amd64, IA64) required by this module
ProcessorArchitecture = ''

# Modules that must be imported into the global environment prior to importing this module
RequiredModules = @()

# Script files (.ps1) that are run in the caller's environment prior to importing this module
ScriptsToProcess = @()

# Format files (.ps1xml) to be loaded when importing this module
FormatsToProcess = @()

# Functions to export from this module
FunctionsToExport = '*'

# Cmdlets to export from this module
CmdletsToExport = '*'

# Variables to export from this module
VariablesToExport = '*'

# Aliases to export from this module
AliasesToExport = '*'

# List of all modules packaged with this module
ModuleList = @()

# List of all files packaged with this module
FileList = @()

# Private data to pass to the module specified in ModuleToProcess
PrivateData = ''

}

You’ll notice that there is a nested module references in this manifest module. This is a reference to a file that contains the function we run in order to kick off the bat file. The ‘GetDacPac.psm1′ should be in the same file location as the psd1 file. However the extension should be psm1.

function Get-DacPac
	{
    [CmdletBinding()]
    param(
        [Parameter(Position=0)]
        [string] $dropLocation = "C:\Users\$env:username\Documents\dacpacBaselines",
        [Parameter(Position=1)]
        [string] $batFile = "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\DacPac\dacpac.cmd"
	)
if(!(Test-Path $batFile))
        	{
            Write-Error -message "[$batFile] does not exist. Cannot execute batch file if it does not exist!" -category InvalidOperation
            return
        	}
if(!(Test-Path $dropLocation))
        	{
            	Write-Warning -message "Creating [$dropLocation] does not exist. Creating folder..."
		New-Item -ItemType directory -Path $dropLocation
        	}
start-process $batFile $dropLocation -NoNewWindow
}
New-Alias bline Get-DacPac
Export-ModuleMember -alias * -function Get-DacPac

The module checks that the cmd file is in the same file directory as the Powershell files and will fail if it does not find it. By keeping all three together you can copy them across servers easily and you also prevents you from having a dozen files strewn across random folders on the servers. The drop location is also checked. The default is a folder in the current users documents folder, and the module will check if the folder exists and creates it if it does not. Both of the defaults for the parameters can be overridden if need be.

The module then executes the batch file and saves the dacpacs to the drop location. Any errors bubble up to the console. It will continue even if one extract fails.

To execute run:

ipmo dacpac
bline

It overwrites any files that exist in the drop location by default. That’s it for now, have a good weekend!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

1 thought on “Extract DacPacs Using PowerShell Via Sqlpackage Revisited”

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