Skip to content

Instantly share code, notes, and snippets.

@SQLvariant
Last active May 12, 2022 18:00
Show Gist options
  • Save SQLvariant/de54887834a54b86ea6ad368f89fdf66 to your computer and use it in GitHub Desktop.
Save SQLvariant/de54887834a54b86ea6ad368f89fdf66 to your computer and use it in GitHub Desktop.
Simple PowerShell function to wrap the SQLPackage.exe command for deployment automation
function Get-SqlChange
{ <#
.SYNOPSIS
This command runs SQLPackage.exe to compare a .DACPAC file to a database and generate a file.
.DESCRIPTION
This command runs SQLPackage.exe to compare a .DACPAC file to a database and generate a either a DeployReport or SQL change file.
.PARAMETER Action
Specify the action, currently only "DeployReport" and "Script" are supported.
.PARAMETER SourceFile
Specify the location of the .DACPAC file you want to compare.
.PARAMETER OutputPath
Specify the full path and name of the file you want created for either the DeployReport or Script.
.PARAMETER SubscriptionName
The subscription used to authenticate the database you are comparing.
.PARAMETER TargetServerName
Fully qualified name of SQL instace you are comparing.
.PARAMETER TargetDatabaseName
Name of of SQL database you are comparing.
.PARAMETER OutputPath
Specify the path you want the file created in for either the DeployReport or Script. The file name will be automatically generated for you.
.EXAMPLE
Get-SqlChange -Action 'DeployReport' -SourceFile 'c:\temp\AdventureWorks.dacpac' -TargetServerName Localhost -TargetDatabaseName AdventureWorks -OutputFolder 'c:\temp'
This will compare the .dacpac file against the AdventureWorks db and generate a DeployReport in SQLCMD format.
.EXAMPLE
Get-SqlChange -Action 'Script' -SourceFile 'c:\temp\AdventureWorks.dacpac' -TargetServerName Localhost -TargetDatabaseName AdventureWorks -OutputPath 'c:\temp\AdventureWorks.SQL'
This will compare the .dacpac file against the AdventureWorks db and generate a change script in SQLCMD format.
#>
[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]
param (
[Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true)]
[ValidateSet("DeployReport","Script")]
[String]$Action = "DeployReport",
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
$SourceFile,
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
$OutputPath,
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
$SubscriptionName,
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
$TargetServerName,
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
$TargetDatabaseName,
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
$OutputFolder
)
process {
if(!$OutputPath){$Comparison = "$(Split-Path $SourceFile -Leaf)_vs_$($TargetServerName.Split(".")[0])_$($TargetDatabaseName)"
switch ($Action) {
'DeployReport' {$OutputFile = "$Comparison.XML"}
'Script' {$OutputFile = "$Comparison.SQL"}
}
$OutputPath = Join-Path -Path $OutputFolder -ChildPath $OutputFile }
cd 'C:\temp\sqlpackage-win7-x64-en-US-16.0.5400.1'
if((Get-AzContext).Name -notlike "$SubscriptionName*" )
{Connect-AzAccount -SubscriptionName $SubscriptionName}
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
Push-Location
if(Test-Path 'C:\temp\sqlpackage-win7-x64-en-US-16.0.5400.1'){cd 'C:\temp\sqlpackage-win7-x64-en-US-16.0.5400.1'}
else {
Write-Warning "Please download the SQLPackage zip file form this location https://docs.microsoft.com/sql/tools/sqlpackage/sqlpackage-download and unzip it into a folder with it's same version-name under c:\temp"
}
if((Get-AzContext).Name -notlike "$SubscriptionName*" )
{Connect-AzAccount -SubscriptionName $SubscriptionName}
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
Write-Output $Action
Write-Output $OutputPath
switch ($Action) {
'DeployReport' {./sqlpackage /Action:DeployReport /SourceFile:$SourceFile /TargetServerName:$TargetServerName /TargetDatabaseName:$TargetDatabaseName /AccessToken:$access_token /OutputPath:$OutputPath }
'Script' {./sqlpackage /Action:Script /SourceFile:$SourceFile /TargetServerName:$TargetServerName /TargetDatabaseName:$TargetDatabaseName /AccessToken:$access_token /OutputPath:$OutputPath }
}
Pop-Location
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment