Deploying a DACPAC

In my last post, we created a DACPAC in SSMS. Now, it’s time to use that DACPAC to create a new database and update an existing database.

Brand Spanking New

What if we want to use our DACPAC to create a new database using SSMS? Our first step will be to right click on Databases and select Deploy Data-tier Application.

After clicking Next on the Introduction, we’ll browse to find our DACPAC to deploy.

The database name can be changed if desired but we’ll leave it as is and click Next.

We can verify that we selected the correct DACPAC by checking the version and description. The summary looks familiar as the DACPAC we recently created.

To deploy our DACPAC, we’ll click Next one more time. If all goes well, our deployment will complete successfully.

Once we click Finish, refreshing Databases will reveal our new database.

Live, Learn, Upgrade

Before applying our DACPAC to the existing StackOverflow2013 database, let’s confirm that StackOverflow2013 does not have our new stored procedure and index.

We only have one stored procedure:

And we only have one index on the Users table:

We’ll right click on our database, hover over Tasks, and click Upgrade Data-tier Application.

Click Next on the Introduction and select the DACPAC to deploy. After clicking Next, objects will attempt to be compared. For our example, the database isn’t registered as a DAC so we get a message that no drift can be detected.

We can choose whether or not we would like to rollback on failure:

We have an index to be created and a stored procedure to be created. Our plan looks good. Time to click Next on the Review window and Next again on the Summary to start the upgrade.

Success! We’ll click finish and refresh the StackOverflow2013 database to confirm our changes.

Build a Batch

A DACPAC can also be deployed via command prompt. To do so, we’ll need to call sqlpackage.exe. I’ve had issues with sqlpackage.exe not being up-to-date with the SQL Server version so you may need to download an updated version to match.

This is an example script that can be saved and ran from a command prompt if we wanted to create another new database:

cd "C:\Program Files\Microsoft SQL Server\150\DAC\bin\"
SqlPackage.exe /Action:Publish /SourceFile:"C:\Users\Chad\Documents\SQL Server Management Studio\DAC Packages\Our_StackOverflow2013_Dev.dacpac" /TargetDatabaseName:CL_StackOverflow2013 /TargetServerName:"CALLIHAN"

We specify the location for sqlpackage.exe, what action we want to take, the location of our DACPAC, and the deployment targets.

We can follow along to see each object get created:

Thanks for reading!