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:
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.
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.
Review the summary and ensure all your details are correct.
Sit back and let it import. The BACPAC I used in this example was a about 25mb and it took a few minutes.
All done! The Import Data-tier Application wizard is now complete. Click close and verify your restore worked.
And there it is! Now you can start your troubleshooting.
Restoring BACPAC via Command Line
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.
- Import/Export SQL Azure Database in Australia
- Error message “The file is either corrupted or does not have the latest credentials associated with recovery service. (ID: 34513) “
- Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (13)
- How to delete ASP.NET temporary files from shared server
- ISAPI Filter ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\\aspnet_filter.dll’ could not be loaded due to a configuration problem
- Moving From No Control Panel Servers to a cPanel Server
- 2 Reasons Why I Am rolling Back From Windows 8.1 to Windows 7
- Locking down and securing SSH access to your server