Restore SQL Azure Backup to MSSQL Server

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.” –

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.

  1. Via the Microsoft SQL Management Tools
  2. 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:

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

Export SQL Azure Database

Export SQL Azure Database

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.


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:

MSSQL Version

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.

Import Data-tier Application

Follow the wizard:

Import Data-tier Application 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.

Import Data-tier Application

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.

Import Data-tier Application Wizard

Review the summary and ensure all your details are correct.

Import Data-tier Application Wizard Summary

Sit back and let it import. The BACPAC I used in this example was a about 25mb and it took a few minutes.

Import Data-tier Application In Progress

All done! The Import Data-tier Application wizard is now complete. Click close and verify your restore worked.

Import Data-tier Application Completed

And there it is! Now you can start your troubleshooting.

MSSQL Server Database List

Restoring BACPAC via Command Line

Thank you to the author (Flea) of the post on Stackoverflow who recommended this process.

You’ll need at least the SQL Server Data Tools – December 2012 update. You can download the “installer” which downloads all the other files for you. I recommend though you download the ISO image, select a Visual Studio and you’ll find the links at the bottom:

Note: Don’t let the words Visual Studio throw you off. As a test, I spun up a fresh Windows 8 VM and installed only the above “Microsoft SQL Server Data Tools 2010 update” and found I could restore the database perfectly. I went with this version in my testing.

Also remember in Windows 8, you can mount ISOs simply by double clicking on them.

Once you’ve installed the update:

Open command prompt and type the following:

cd C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin

SqlPackage.exe /a:Import /sf:D:\path\to\file.bacpac /tdn:Destination-DB-Name /TargetServerName:(local)

This will restore the BACPAC (D:\path\to\file.bacpac) to database Destination-DB-Name on server (local).

This may not suit your needs so check out the parameter options for SqlPackage.exe.

I hope this helps people out.

Similar Posts:

VN:F [1.9.22_1171]
Rating: 5.0/5 (4 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 3 votes)
Restore SQL Azure Backup to MSSQL Server, 5.0 out of 5 based on 4 ratings
Tags: , , , , , .

10 Responses to Restore SQL Azure Backup to MSSQL Server

What are your thoughts?