Import Sitecore Azure DB to SQL Server DB … illustrated

So you wanna import the Sitecore Azure DBs into SQL Server …

Sitecore introduced Microsoft Azure support in version 6.3 which meant flexible and unlimited scalability, content delivery across the world, real-time support of features like failover and backup among other pluses.

Recently, I had the pleasure of working with such a system for a Sitecore upgrade and have documented my questions/hurdles that I overcame when recreating the Sitecore web application on my local machine.

Below is the journey that I took to restoring the Sitecore production databases down to my local database server.  Although there are Azure database interfaces available, I choose to use good-ole SQL Server Management Studio to back up.  Then, to import the db into SQL Server, I attempted to use the studio for SQL Server 2012, version 11.0.3156.0.  HINT:  If you don’t want to repeat my mistake, read this entire post before following along.  🙂

1. Back up

The first step is to take a back-up of the Azure database.  There’s nothing too special about how to perform this step in SQL Server Management Studio.  Run the back-up task for each database.  However, the output file has a different format;   instead of a .bak file, a .BACPAC file is created.  This is a logical backup Azure file of the schema definition and the database’s table data.

2. Time to restore (well, import)

On the Databases node of your server, choose Import Data-tier Application … and follow the wizard (seems simple enough).

What is a data-tier application or DAC?
It’s “.. an entity that contains all of the database and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects instead of having to manage them separately. A DAC allows tighter integration of data-tier development with the development of the associated application code. It also gives administrators an application level view of resource usage in their systems.”- https://technet.microsoft.com/en-us/library/ee240739(v=sql.105).aspx

Pic 1

Pic 2
This is me breezing thru the steps …

I was so confident when going through the wizard, only to have it bite me at the end.  It would have been nice to be warned on that Intro wizard screen, eh? Something like, “Note: The Database Schema Provider only supports file versions up to x.x.” Yeah, you’re right; I wouldn’t have necessarily looked up what schema file version of Azure I was importing but nonetheless …

Pic 4Let’s add an additional step and re-do the last step, shall we?

2. Install a new version of SQL Server Management Studio.

I chose SQL Server Management Studio December 2015 preview as it was the latest version available when I was working on this:

https://msdn.microsoft.com/en-us/library/mt238290.aspx

Pic 5
You’ll need a later version of SQL Server Management Studio

3. Time to import (again)

This time… it worked!  Rinse and repeat for each database.

Pic 7
Success

Here’s hoping that this makes your import much smoother than mine.  In my next post, I’ll be putting the upgraded, SQL Server databases out into Azure (yep, another hurdle).  Stay tuned!

Reference:  http://stackoverflow.com/questions/28566610/unable-to-import-sql-azure-v12-bacpac-type-microsoft-data-tools-schema-sql-sql