SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Create a BACPAC–#SQLNewBlogger

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

One of the things that you might encounter at some point is the need to create a BACPAC. This is essentially a DACPAC with data included. There are times you might find a BACPAC more convenient than a full backup, especially if you work with Azure.

This post shows how you can create a BACPPAC file (with the extension .bacpac) from SSMS.

I have a small database, the PartsUnlimited database, from the Microsoft PartsUnlimited demo application. I want to create a BACPAC from this, however, when I right click and select Tasks, there is no “Create BACPAC” option.

2017-02-21 14_08_41-SQLQuery1.sql - dkranchlabdemo.database.windows.net.master (sjones (113)) - Micr

That’s fine, because the “Data Tier Application” is the DAC, and an export includes the data, which creates the BAC part of the process. Let’s choose “Export Data-tier Application”. Once we do that, we get the expected summary wizard screen to start.

2017-02-21 14_10_05-Export Data-tier Application 'PartsUnlimited'

I click next and immediately get prompted for a location in which to save the file. The will include all your data, so choose a location that has sufficient disk space. You have the option to also store this in Azure if needed.

2017-02-21 14_10_43-SQLQuery1.sql - dkranchlabdemo.database.windows.net.master (sjones (113)) - Micr

Note the filename above is already filled out by default, but you can change this in the edit box. If you click the “Advanced” tab, you’ll get the chance to select only certain tables if that is required. You can see this dialog below.

2017-02-21 14_10_50-Export Data-tier Application 'PartsUnlimited'

Once you’ve chosen a location, you click next and get a summary page.

2017-02-21 14_13_31-Export Data-tier Application 'PartsUnlimited'

Click finish, and the process starts. This runs very quickly, extracting the schema and then all the data, noting the results of each object.

2017-02-21 14_13_38-Export Data-tier Application 'PartsUnlimited'

That’s it. If I look in the location, I’ll see my BACPAC file.

2017-02-21 14_15_11-DAC Packages

I can copy this to another machine and import it to recreate a database. We’ll do that in another post.

SQLNewBlogger

A short, quick post. I’ve done this before, but I had to do this for a quick process and took 5 extra minutes to take screen shots, spending 5 minutes later writing this up.


Filed under: Blog Tagged: backup, SQLNewBlogger, syndicated

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...