Azure: Creating a BACPAC from an On-Premises SQL Server Database

Leave a comment

September 13, 2016 by Kenneth Fisher

So to start, what’s a BACPAC?

bacpac1

Per the Azure documentation

When you need to create an archive of an Azure SQL database, you can export the database schema and data to a BACPAC file. A BACPAC file is simply a ZIP file with an extension of BACPAC. A BACPAC file can later be stored in Azure blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database or into a SQL Server on-premises installation.

Note: I’ve also seen references to BACPAC files as a data-tier application file or a DAC.

Why are we talking about it?

Well there are two reasons. First because I’m studying how to move databases from SQL Server to Azure SQL Database and back. My first blog on the subject was using the Deploy Database to Microsoft Azure SQL Database option to move a SQL Server database to Azure SQL Database.

T-SQL Tuesday
The second reason is because this is TSQL Tuesday! and Jeffrey Verheul (b/t) has asked us to talk about the Cloud. And specifically Azure SQL Database. So it fit in perfectly with my plans anyway.
 

Our end goal is to use the BACPAC in the cloud so let’s create some Azure cloud storage to store it.

Create Azure cloud storage

Once we are logged into the Azure portal (and I’m not going to go over a Powershell option for this portion although I’m sure it exists).

  1. Select More Services and search for Storage
  2. Open Storage Accounts and click on the + Add button.
  3. Enter the name, subscription and resource group. (There are several other options but I’ve chosen the defaults for all of them.)
  4. Hit Create.
  5. After a minute or two hit refresh and your storage group should be there.
  6. Click on the storage group to open it.
  7. Click on Blobs.
  8. Click on + Container.
  9. Name the storage container.
  10. Select the storage type.
  11. Hit Create.

azurecreatestorage

 

Create a BACPAC file

There are a number of restrictions here. For example when I tried to move one database it failed every time because I had users mapped to windows logins which aren’t allowed. I wasn’t able to find a list of restrictions however if you remove the problem objects then the export works just fine.

There are several ways to handle this.

GUI
  1. Right click on the database.
  2. Tasks then Export Data-tier Application.
  3. Next
  4. Save to Microsoft Azure.
  5. Connect
  6. Get storage account and account key from Azue and enter them.

    bacpac2_getkeys

  7. Select the container
  8. Next
  9. Finish
  10. Wait for the process to finally complete.

 
azureexportbacpac
 
From what I’ve read this does have a restriction of databases 200gb or under. Anything higher and you will have to use SQLPackage.exe. I did look for a Powershell solution but all I could find was code to export an Azure SQL Database to a BACPAC, not a SQL Server database. If I find one I’ll post about it later.

SqlPackage.exe
-- Parameters 
sqlpackage.exe /Action:Export /ssn:{server name} /sdn:{database name} /tf:{target file}
-- Example
sqlpackage.exe /Action:Export /ssn:(local)\sql2014cs /sdn:AdventureWorksDW2014 /tf:c:\temp\AdventureWorksDW2014.bacpac

SqlPackage has a lot of options so I’m just giving the basic Export command. I would assume there is a way to move the file directly to the Azure storage but I haven’t figured that out yet. Once it’s created locally you can move the file up to the Azure storage using AzCopy.

-- Parameters 
AzCopy /Source:{source directory} /Dest:https://myaccount.blob.core.windows.net/mycontainer /DestKey:key /Pattern:"abc.bacpac"
-- Example
AzCopy /Source:C:\temp /Dest:https://sqlstudiesvmstorage.blob.core.windows.net/blobcontainer /DestKey:key /Pattern:"AdventureWorksDW2014.bacpac"

The location of the container and key can be found above.


So far so good! Next time I’ll take a look at importing the BACPAC using the GUI, sqlpackage.exe and Powershell (I was able to find this part).

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,615 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: