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)
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.