How to Restore Microsoft SQL Azure Backup to Microsoft SQL Server

I’ve been exploring Microsoft Azure more and more recently. One of the most common pains I’ve seen though is getting an SQL Azure backup into to a local development environment for troubleshooting.

Of course, SQL Azure database backups are not straightforward .bak files, no no, they’re .bacpac files. What is a bacpac? Good question, I had to check myself:

“The SQL Database Import/Export Service creates a logical backup (BACPAC) file containing the schema definition and table data of a database in Windows Azure SQL Database. You can then recreate the database on another Windows Azure SQL Database server or an on-premise instance of the SQL Server Database Engine.” – http://msdn.microsoft.com/en-us/library/windowsazure/hh335292.aspx

A significant portion of my searching has turned up mounds of results about moving SQL Server Databases to SQL Azure, but not the other way around. There were some subtle hints of it in the Microsoft documentation but it wasn’t obviously.

This blog post is intended to make it obvious. I found two methods of restoring a .bacpac file to Microsoft SQL Server Database Engine.

Via the Microsoft SQL Management Tools
Via command line.

I know Microsoft SQL Server 2012 is out and about, and that’s really nice. But the majority of the business world is still invested in Microsoft SQL Server 2008 R2 or earlier. I also want to be clear that the below instructions were conducted on:

Server Version: Microsoft SQL Server 2008 R2 SP2 Standard
Management Tools Version: Microsoft SQL 2012 Management Tools

I suspect the server version will be portable at a guess to 2008, maybe 2005 – I’ve not tested it.

A common problem with restoring MSSQL Server .bak files is you cannot restore them to earlier versions of MS SQL Server (e.g. 2008 R2 to 2008, or 2008 to 2005). However I’m so far impressed at the ability to be able to restore the .bacpac files to MSSQL Server 2008 R2. Please, test it out on earlier versions and let me know in the comments how you go.

Backup your SQL Azure Database

This process is already very well documented so I’m not going to rewrite it. Check these out:

http://msdn.microsoft.com/en-us/library/windowsazure/jj650016.aspx

http://msdn.microsoft.com/en-us/library/windowsazure/ff951631.aspx

I personally found it ~~easy and straightforward~~ preferable to use the Azure Management Portal.

I generally Export the .bacpac to blob storage and download it to the dev environment from there. I’m sure it’s not the best method but it’s one.

I use and recommend CloudBerry Explorer Free for Azure. It allows you to connect to Azure Blob storage and transfer files to and from. The product developer CloudBerry Lab has some other good products worth checking out too.

Restoring BACPAC with Microsoft SQL Management Tools

As I mentioned, there’s a small catch – you need the Microsoft SQL Management Tools from SQL 2012. You simple need a release version (not the release candidate) of the Management Tools, even the Express edition works fine.

http://www.microsoft.com/en-au/download/details.aspx?id=29062

ENU\x64\SQLManagementStudio_x64_ENU.exe
ENU\x86\SQLManagementStudio_x86_ENU.exe

Once again, to be clear, the server I’m restoring to is Microsoft SQL Server 2008 R2 SP2 Standard but the Management Tools are 2012. You can have both the 2008 R2 and 2012 Management Tools installed side-by-side.

I’ve not tested this on other SQL Server versions, I’m sure it will work though. Please test and let me know in the comments.

Confirmation of the Management Tools version I’m using:

Connect to your MSSQL Server. Ensure you’re using an appropriately permissioned account – I use an account with sysadmin role.

Right mouse click on Databases and click Import Data-tier Application. Yes, I know, it looks unusual. Just roll with it.

Follow the wizard:

Select the path to your downloaded BACPAC file. If you’ve not downloaded it from Azure, see the section above on tools for downloading out of blob storage.

Alternatively, you can import from Windows Azure right from the wizard however it is incredibly slow depending on your location.

Give the database you’re restoring a name and define a location for the data and log files. The default location was wrong for me, I had to define the paths below where I normally store my respective files.