Windows Azure Backups with Bacpac–Automating Exports (Part 2)

This is part 2 of my azure backups “series”.  This will go into doing exports of an Azure database to Bacpac file in blob storage.  Part one just went over a couple of options for backups and how to do it manually.

Today we’ll be looking at the simple case for exporting your databases.  By simple I mean you just have your database and no DataSync enabled on the database.  If you are in the situation where you are using DataSync, I’ve got a solution, it’s ugly but it works (you’ll have to wait for part 4).

The basic idea is that we’re going to use the API to command Azure to export the database to a storage container.

I based my code on the following blog by Colin Farr:

http://www.britishdeveloper.co.uk/2012/05/export-and-back-up-your-sql-azure.html

It’s a great article and goes over how to do it.  Honestly, my code doesn’t change a whole lot, so a lot of the credit goes to Colin on his excellent example.  I do give the option of doing selective export but that’s very minor.

This is only the function for performing the backup, I have rolled it into a whole class to be used with some clean up.

You basically pass in the name of the database, (optionally) a list of tables you’d like to export and it’ll give you back a status id and a filename that it saved the backup to.  All of the credentials are setup through the constructor (not shown), but you could easily hard code that into the function if you wanted.

That’s it for now.

Azure Backups Series:

Windows Azure Backups with Bacpac (Part 1)

Backing up or snapshotting a Windows Azure database seems like it should be a pretty straight forward affair and in reality it is, unless you have a more than simple situation.  Whether you just want to have a copy of your data locally or snapshots in time, it shouldn’t cost you an arm and a leg to maintain.

There are several solutions floating around out there as to how to accomplish backups.  Here are just a few that you can look into if you care to (these are just the ones that don’t involve paying a 3rd party vendor to implement):

  1. Database Copy
  2. Azure Data Sync
  3. Export to Bacpac
        You can read up on

Microsoft’s official suggestions here.

    Personally, I sort of have a problem with option 1 and 2.  They both require you to have another SQL server running (either an Azure or Local Database), bottom line is that if you are using an Azure database, you are essentially doubling your costs.

That leaves us with exporting to Bacpac file.  I like this option since it relies on the relatively cheap blob storage and gives you the option to just download the file and save it locally.

Manual Method

The simple way to export your database to Bacpac files, is to simply open up SQL Management Studio and follow these steps:

  1. Connect to your Windows Azure database server
  2. Right click on the database
  3. Select “Tasks”
  4. Select “Export to Data Tier Application”
  5. Under the Export Settings, select Windows Azure and fill out the credentials
  6. Celebrate!

You can also perform the export from the Azure Management Console:

  1. Log in to the Azure Portal http://windowsazure.com
  2. Select Database
  3. Click on Export, fill out credentials.

That was easy, right?  But who wants to actually do this manually every day or every 6 hours?

DataSync and Bacpac

There is one case that has been driving me up a wall.  Part of our database uses Data Sync to get some on-premises look up data into our Azure Database.  This brings up a problem, since DataSync adds some miscellaneous tables and stored procedures.  The stored procedures triggers added to your tables are incompatible with Bacpac and causes an error on import.  It doesn’t give an error on export, but the second you try to import it throws an error about semi-colons missing.

Microsoft seems to be aware of  this issue, but I’m not seeing any traction on a fix.  There is an open ticket about this.

Update 1/11/2013:  It would appear this issue has been resolved with restoring a bacpac file that previously had DataSync enabled.  However, when you try to re-enable DataSync to the database it can be extremely slow depending on the amount of data being synced.  With approximately 1 gb of sync data, it took more than 4 hrs before I gave up.  It really depends on your recovery situation.  If you must have the database restored ASAP and are not concerned about a little stale data you can do a plain restore and the original tables with data will still be there.  However I prefer to have the latest data from the DataSync available so in Part 4, I have a solution for that.

Automating Bacpac Export

Doing the exporting manually just isn’t an option for most of us. So in the coming posts I’ll be showing the following:

  • Part 1 : Basic Introduction to Azure Backups
  • Part 2 : Automating Exports
  • Part 3 : Automating Cleanup
  • Part 4 : Automating Exports with Data Sync enabled