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.

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:<strong>%windir%\System32\WindowsPowerShell\v1.0\Modules</strong>. Save the module with the psd1 extension

powershell#

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.

powershell 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:

powershellipmo dacpac bline```

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