Once you go cloud, there is no going back.  This is false and from a database perspective you can migrate back to your own set of servers.

The Challenge: I am going to write about a way to move from Azure SQL Database (Platform as a service) back to a local SQL Server. I did encounter errors on the way but more importantly I have written how to avoid/solve them.

Another key point I made sure that there were no connections to the database when doing the below as I didn’t want in-flight data movement whilst doing it. If you can’t do this, then you should create a copy of the database and work from that.

Get a BACPAC

Using SSMS (SQL Server Management Studio) I connect to my SQL database of interest (which is in Azure).

Right Click on the database find tasks and hit that “Export Data-tier Application” option.

You have 2 options here, save the BACPAC in an Azure storage container or to a local disk; I am going for BACPAC to local disk option because it is less hassle.

Do not forget to check the advanced tab – here you confirm what data you need.

Click the finish button and let it do its job – this was a slow process for me, it very much depends on the size of the data movement and your connection. What you want to see is the green tick!

If you need a more detailed guide on BACPAC creation, then Steve Jones has a greate post: https://voiceofthedba.com/2017/03/15/create-a-bacpac-sqlnewblogger/

So now you have your bacpac file, move that file to the local disk on your SQL Server then connect to it via SSMS.

Right click on your database node and select import Data-tier Application and find your bacpac file.

For the sharp readers out there I bet you guessed what went wrong a few screen shots ago? It was the wrong version of SQL Server! I got all sorts of messages around SQL database V12 compatibility. So, let’s work with a local SQL Server 2016.

A bad workman always blames his tools

So I went through the same process as shown above where I imported the bacpac file into my 2016 SQL Server, don’t try and use an older version of management studio you will get internal errors when trying to import, such as:

So I started over with the import using 2016 management tools but then I encountered a different error. Warning SQL72014. Net SqlClient Data Provider message as shown below.

The target server must have containment set on because in Azure SQL database I used contained users.

On the target/local 2016 server I had to run the following.

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

When contained database authentication is set to 1 for the instance, contained databases can be created or attached to the database engine.

I had my database structure (very simple structure I know), the actual data and even the SQL login(s) all locally.

This is why I love contained users; the portability concept makes life easier.

Thinking about it at a high-level I literally did the reverse of a local SQL Server to Azure migration.

Hopefully this post will be useful for some of you out there and shows you that you can get your data back to your own servers – if need be.

Filed under: Azure, Azure SQL DB, Cloud Blog Series Tagged: Azure, Bacpac, Cloud Busting, Migration, SQL server