CODEWITHSUNDEEP
sql-serverpowershellcontinuous-integrationcontinuous-deploymentdacpac
Sunil Buddala
Sunil Buddala

Reputation: 1231

Deploy multiple dacpac files to a single database

I want to automate my database deployment on our CI server. I wrote a powershell script to deploy a dacpac file to the the database on the sql server. But now I am in a strange position where I have two separate dacpac files and want to deploy them to a single database (I cant use a single dacpac due to our software design). If I deploy them one by one, it removes the objects created from the first dacpac. So any help here is highly appreciated.

If there are other ways to combine two dacpac files and deploy them using any commands or scripts, please reply your suggestions.

I am using the below powershell script to deploy single dacpac to the database.

$sqlPackagePath = "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll";
$sqlServer = "localhost";
$dacpacPath = "mydatabase.dacpac"
$database = "TestDatabase"

add-type -path $sqlPackagePath
$dacService = new-object Microsoft.SqlServer.Dac.DacServices "server=$sqlServer"
$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpacPath)
$dacService.deploy($dp, $database, "True")

Upvotes: 2

Views: 2642

Answers (1)

Steven Green
Steven Green

Reputation: 3507

By default, publishing a dacpac does not drop extra objects. There's a setting that you must explicitly choose to enable that behavior (it's called "drop objects not in source" and it's false by default).

You are, though, using an old (SQL Server 2012) version of Microsoft.SqlServer.Dac to perform this operation so perhaps the behavior of that version differs from the newer versions.

The latest version of the Data-Tier Application Framework (Microsoft.SqlServer.Dac) is available here: https://www.microsoft.com/en-us/download/details.aspx?id=55088

After installation the new version of Microsoft.SqlServer.Dac will be in Microsoft SQL Server\140\DAC\bin

Upvotes: 4

Related Questions