Backing Up Azure SQL Locally

Azure SQL Database Export FAIL

Let me begin with the moral of the story: Azure isn’t the answer for everything. The cloud is still new, and the closer you get to the bleeding edge, the more time you’ll be spending trying to work all of this stuff out.

We have a client who started with another consultant. Over the past few weeks, we’ve evaluated where they left things, and decided that the path the previous consultant took may not be the best long-range path. So we need to grab their database (which is an ERP database with about 50,000 tables spread across about 125 companies) from SQL Azure.

Thus began the saga.

The “easy and complete” way to do this is using SQL Server Management Studio. Select the database, go to Tasks > Export Data Tier Application… Fill in the blanks (where do you want the bacpac file? What tables to do you want? Then start the process. And Wait. And Wait. And Wait.

Eventually, you get a timeout error. When you look this up the first time, you’ll see an option to edit the registry and increase the timeout. That was two weeks ago, and I’ve forgotten the registry key, but Google the error and you’ll go right to it after a page or so of results.

Try again, and you might be lucky. I wasn’t. I even tried selecting only a few hundred tables. Same result. I increased the timeout. Same result. By the way, there were some 4-8 hour waits before the timeout occurred, so you’ll be spending a week or so with this solution.

After you try that a time or to, you’ll decide to go to SqlPackage, a command line utility that allows you to do the same thing, with more control.

Here’s what my final try of this command looked like:

sqlpackage.exe /a:Export /of:True /sdn:DATABASE_NAME /su:USER_NAME /sp:PASSWORD /ssn:asm-srv-azsql-01.database.windows.net /tf:c:DataMY_FILE_NAME.bacpac /p:CommandTimeout=6000

Yep, that’s a 6000 second timeout (default is 30).

Connecting to database ‘DATABASE_NAME’ on server ‘SUBDOMAIN.database.windows.net’

Unlike some of the other tries, this actually produced a (small 25K) file on my desktop. I let it run 72 hours before I stopped it, and stopped it when I noticed that nothing had been written to the file in 48 hours. Over the weekend, I tried again.

Here’s the message that was waiting on me this morning:

Azure SQL Database Export FAIL

Messages from SQLPackage.exe when trying to export Azure SQL Database

Note that time elapsed. That’s 18 hours.. It seems like that’s where we came in.

And now to figure out another way to get the data…

2 thoughts on “Backing Up Azure SQL Locally

  1. In Azure you should back the database up to a storage container / service. From that storage container / service you have an easy way to download the backup.

    • Correct. That works IF you have access to the Azure portal for the database. In this case, another consultant provided access via SSMS.

      In addition, Microsoft’s own documentation points to SSMS backup to local as a solution.

Comments are closed.