Create a DACPAC to Move Databases–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In my last post, I talked about what a DACPAC was. Now let’s create one. This turns out to be about as simple as it could be. First, let’s choose a database. In my case, I’ll use the PartsUnlimited database on my SQL Server 2016 instance. I’ll start by right clicking the database and selecting “Tasks”.

2017-03-03 11_40_53-

Down near the bottom there is an “Extract Data-tier Application” entry. Choose that. Once you do, you should get a wizard screen. We’ll click past the first screen.

2017-03-03 11_43_04-Extract Data-tier Application

Next we need to set the properties of the DACPAC we are building. The application name is usually the database. The version can be anything, but ideally you are versioning your database in some way. Most people don’t, so they can leave this as 1.0.0.0. If you are actually building your database somehow, you ought to be using a CI process and have some version number. Ideally you’d keep this somewhere in the db and rev it.

We also need to include an optional description and a path to the file. I’ll leave the defaults, but feel free to change these as needed.

2017-03-03 11_44_54-Extract Data-tier Application

The next screen is a summary. If everything looks OK, click Next. This will start the creation of the .DACPAC.

2017-03-03 11_45_02-Extract Data-tier Application

The final screen will show progress, which is fairly short and simple. I think this has always worked for me. If I click the “Finish” button at the bottom (not shown), the dialog disappears.

2017-03-03 11_46_16-Extract Data-tier Application

If I go to the file location, I’ll see my DACPAC in the filesystem. The file is recognized as an SSMS file, and in another post we’ll look at how we unpack this.

2017-03-03 11_47_19-DAC Packages

I can check this file, however, to be sure there is something inside it. This is a zip file, and if I open it in 7-Zip, I see this:

2017-03-03 11_48_18-e__Documents_SQL Server Management Studio_DAC Packages_PartsUnlimited.dacpac_

Opening the model.xml file, I see data that seems like it describes my database. I talked about this in my previous post.

2017-03-03 11_48_33-model.xml - Visual Studio Code

There, a simple DACPAC. This is a format Microsoft uses, and while it’s not perfect, at least I now know how to build one.

SQLNewBlogger

This post really took me about 10 minutes, across a few days. I started it after building a DACPAC, and taking the screenshots, but I didn’t have time to write it. I finally spent the other 6 or so minutes putting these words down the next week.

A quick showcase of something I learned.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

One Response to Create a DACPAC to Move Databases–#SQLNewBlogger

  1. paschott says:

    Just a quick note – this works for pretty straightforward DBs, but if you have any cross-DB procs/views/functions, you could have issues using this method. I had to resort to using the SQLPackage command line in those cases.

    Agreed that the dacpac isn’t perfect, but it does a pretty good job of detailing the DB objects in a compact form. It’s a lot more efficient than the old DB Project files, probably due in part to the compression.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s