How To Move an Azure SQL Database to a Local Instance

 

Today a software developer friend has requested to move a database in Azure SQL Database to a local server with its data.

What I did was to export the SQL Database to a available Storage in Azure, and then download the generated * .bacpac file to an appropriate server in the local environment, and then Restore to the local SQL Server Instance.

What I would like to mention in this article is the ingenuity of the * .bacpac file. The version of the database in Azure SQL Database was “12.0.2000.8”. The Instance version I restored the *.bacpac file was “11.0.5582.0”.

As each experienced SQL Server DBA knows, “* .bak” files that were created in a higher version cannot be restored to a lower version.

To be more precise, and if we go over this example, a * .bak file that you created in an Instance that is in version 12.0.2000.8 cannot be restored to an Instance with version 11.0.5582.0. However, a * .bacpac file created in version 12.0.2000.8 can be successfully restored to an Instance with 11.0.5582.0 version.

However, when I tried to perform this Restore (actually Import) directly from SQL Server Management Studio on 11.0.5582.0, I received an error:

TITLE: Microsoft SQL Server Management Studio
——————————
Count not load schema model from package. (Microsoft.SqlServer.Dac)
——————————
ADDITIONAL INFORMATION:
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)

This issue was related to the version of SQL Server Management Studio rather than the * .bacpac file. When I tried to import the same * .bacpac file in a newer version of SQL Server Management Studio, the operation completed successfully without any problems. I would like to note here if someone encounters such a mistake.

Ekrem Önsoy
Author: Ekrem Önsoy

The original article was written in Turkish by Ekrem Önsoy and translated to English by dbtut with the consent of the author. The copyright of the article belongs to the author. The author shall not be liable in any way for any defect caused by translation.

Leave a Reply

Your email address will not be published. Required fields are marked *