Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

February 7, 2014

Deploying a DACPAC with SQL Server Management Studio

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using SQL Server Management Studio.

Many DBA’s are more comfortable using SQL Server Management Studio. So if the developers provide a DBA with a DACPAC, the DBA may prefer to use SQL Server Management Studio to deploy it.

This post covers the steps for using SQL Server Management Studio 2012 to deploy a DACPAC. For SQL Server Management Studio 2008 R2, the steps are different and it only supports older versions of DACPACs. However, you CAN use SQL Server Management Studio 2012 to connect to an older SQL Server instance, such as SQL Server 2005 or 2008 R2, and deploy a DACPAC.

Here are the steps for deploying a DACPAC with SQL Server Management Studio 2012:

  • Open SQL Server Management Studio.
  • Connect to the SQL Server Instance containing the database to deploy to.
    • Notice that in the example below, I am connecting to a SQL Server 2008 R2 SQL Express instance.
  • Navigate the tree in the Object Explorer to the database to deploy to.
  • Right-click on the database name and select Tasks | Upgrade Data-tier Application…

image

  • The Upgrade Data-tier Application wizard is displayed. It presents the set of steps that will be executed by the wizard.

image

  • Click Next.

image

  • Use the Browse button to find and select the DACPAC file. It is located in the Bin\Debug directory of the database project on the developer’s machine. Or where ever it was put if the file was provided to a DBA or other individual.
    • The default expected location is: C:\Users\<userName>\ Documents\SQL Server Management Studio\DAC Packages
  • Click Next.

image

  • The information in the DACPAC is compared with the target database.
    • If the target database was changed externally from a DACPAC deployment (by making changes directly to the database), you are warned that the database was changed.
    • You can optional proceed anyway, or exit the wizard and research the issue.
  • Click Next.

image

  • The deployment script is automatically generated.
  • From the above dialog, you can optionally select to save the deployment script to a file.
  • When ready, click Next.

image

  • The Upgrade Plan is then displayed for review.
    • You can elect to save the report.
    • You again have the option to save the generated script.
  • Click Next.

image

  • A summary is displayed containing the information that will be used during the deployment.
    • Note that in the above screen shot, I opened each node in the summary so you can see the details.
  • When you are happy with the summary information, click Next and the deployment process (finally!) begins.

image

  • The generated script is executed, completing the DACPAC deployment process.
  • If desired, click Save Report.
  • Then click Finish.

If you compare this process to the steps for deployment using Visual Studio, you can see that there are MANY more steps. But the DBA may be happy with all of these extra steps because they provide additional verification before deploying the DACPAC.

Enjoy!

For more information on this and other SQL Server Data Tools (SSDT) features, see my latest Pluralsight course: "Visual Studio Data Tools for Developers", which you can find here.

4 Comments

  1.   BigB — December 6, 2016 @ 11:30 pm    Reply

    Hi, I have created a DB project in VS2015 and it was build successfully and I have deployed it on ABC database. After I created a snapshot from DB project. When I started following your article steps I am getting below error after I select .DACPAC file and click on next. Any idea?

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Database source is not a supported version of SQL Server “XXX”: . (Microsoft.SqlServer.Dac)

    ——————————
    BUTTONS:

    OK
    ——————————

  2.   Dave Smith — March 19, 2017 @ 12:25 pm    Reply

    I’m surprised to learn SSMS does not show the Advanced Options dialog as VS does when creating the publish profile. I expected it to be “annoying” that I would have to set those each time I deployed from SSMS but it turns out those options do not appear to be available within SSMS. Is it true that the only way to set those options is to use SqlPackage with a Publish Profile?

    •   deborahk — March 27, 2017 @ 1:46 pm    Reply

      Over these past two years my focus has been on client-side development (Angular specifically) … so I have not been keeping up with SSMS or SQL data tools. Try posting to stackoverflow instead.

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs