Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Script to publish SQL Server database dacpac using PowerShell and SQLPackage.exe
#=================================================================================
# Designed to deploy a database from a dacpac
#
# Usage:
# .\sqlPackageDeploymentCMD.ps1 -targetServer "LOCALHOST" -targetDB "IamADatabase" -sourceFile "C:\ProjectDirectory\bin\Debug\IamADatabase.dacpac" -SQLCMDVariable1 "IamASQLCMDVariableValue"
#
# So, why would you do this when you could just call the sqlpackage.exe directly?
# Because Powershell provides a higher level of orchestration; I plan to call this script from another script that
# first calls a script to build the dacpac that is then used in this script.
#=================================================================================
[CmdletBinding()]
Param(
#SQLPackage
# This directory for sqlpackage is specific to SQL Server 2012 (v11).
[Parameter(Mandatory=$false)]
[string]$sqlPackageFileName = "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe",
#Database connection
[Parameter(Mandatory=$false)]
[string]$targetServerName = "LOCALHOST",
[Parameter(Mandatory=$false)]
[string]$targetDBname = "IamADatabase",
#DacPac source
#Note PSScriptRoot is the location where this script is called from. Good idea to keep it in the root of
# your solution then the absolute path is easy to reconstruct
[Parameter(Mandatory=$false)]
[string]$sourceFile = """$PSScriptRoot\ProjectDirectory\bin\Debug\IamADatabase.dacpac""", #Quotes in case your path has spaces
#SQLCMD variables
[Parameter(Mandatory=$false)]
[string]$SQLCMDVariable1 = "IamASQLCMDVariableValue",
[Parameter(Mandatory=$false)]
[string]$SQLCMDVariable2 = "IamSomeOtherSQLCMDVariableValue",
)
& "$sqlPackageFileName" `
/Action:Publish `
/SourceFile:$sourceFile `
/TargetServerName:$targetServerName `
/TargetDatabaseName:$targetDBname `
/V:SQLCMDVariable1=$SQLCMDVariable1 ` #If your project includes other database references, or pre/post deployment scripts uses SQLCMD variables
/v:SQLCMDVariable2=$SQLCMDVariable2 `
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.