Blogs‎ > ‎

Restoring a Microsoft Azure 'bacpac' file to SQL Server 2008 R2

posted 23 Feb 2016, 16:50 by Andrew at Lycom   [ updated 23 Feb 2016, 16:51 ]
Today I got a job to transfer an IIS + SQL site from an Azure hosted environment to a 'real' server (Windows 2008 R2).  All went well until I came to 'restore' the SQL backup file into the new SQL DB set up on the server (running SQL Server 2008 R2). Fool, did you think that would work?

bacpac - uh-oh!

Ah. Not good. A bit of googling lead me to this helpful post:

https://sysadminspot.com/server-administration/restore-sql-azure-bacpac-to-microsoft-sql-server-database-engine/

So I installed the SQL 2014 Management Tools (NOT the full version, JUST the management tools). Another helpful link to the individual downloads:

http://www.hanselman.com/blog/DownloadSQLServerExpress.aspx

Choose the 'New Sql Server 2014 Stand-alone install' option (and NOT the UPGRADE option). It _will_ just_ install the console tools, and not affect your current SQL Server 2008 Setup.

Then use the 'import data-tier application' method described in the above link.

I found I had to create a NEW database, that gave me an error:

Could not load schema model from package. (Microsoft.SqlServer.Dac)

Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service. (Microsoft.Data.Tools.Schema.Sql)

So I installed both the x86 and 64 versions of Microsoft® SQL Server® Data-Tier Application Framework (February 2015)

Re-tried the import process above and was able to restore the bacpac backup into my SQL Server 2008 R2 setup as a new DB.

Not exactly a straightforward process, but it does kind of work.  Hopefully I won't get too many of these to do in future.