So to start, what’s a BACPAC?

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.

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.

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.

   

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

 

 
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).