Restore Azure Managed Instance to Azure SQL Database

Nigel Morse 191

Is it possible to take a restore a backup from a managed instance to an Azure SQL database? Sometimes I'd like to be able to restore a copy of a large database for a few days, or for testing some new functionality. We'd like to separate this from the managed instance so being able to restore a copy to a Azure SQL Database easily would be awesome.

1 answer

Anurag Sharma 17,606 Reputation points

2020-11-17T04:17:54.127+00:00

Hi @Nigel Morse , welcome to Microsoft Q&A forum.

The easiest approach would be to store the database backup from bacpac to Azure SQL Database:

1). Export Azure SQL Managed Instance to a bacpac file
https://learn.microsoft.com/en-us/azure/azure-sql/database/database-export

2). Import from the bacpac file to Azure SQL Database
https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import?tabs=azure-powershell

But this is an approach that will create database from scratch.

Please let me know if this helps or else we can discuss further on the same.

----------

If the answer helps, please select 'Accept answer' as this could help others having similar queries.
Please sign in to rate this answer.
Nigel Morse 191 Reputation points

2020-11-18T11:58:52.843+00:00

Hi,
We've looked into BACPAC before - but these are not a viable option for a large database. Ours is now on the order of 400GB and creating a bacpac on a decent machine took maybe 12 hours. Restoring that was taking even longer. I suppose I could try it on azure just to compare and confirm.

It frustrates me that all the literature I can find always comes down to bacpacs when just allowing systems to use native backups would be so much better.

Anurag Sharma 17,606 Reputation points

2020-11-20T08:18:54.077+00:00

Hi @Nigel Morse , you are right in mentioning that the time taken for this process will be proportional to the size of database.

And we have the current limitation where in we cannot restore the database from .bak file or native backups. However this would also be dependent on database size but can significantly reduce the time taken to convert it to bacpac.

As you mentioned, could you please try on Azure and see if that takes less time comparatively.

Nigel Morse 191 Reputation points

2020-11-20T10:27:10.39+00:00

but can significantly reduce the time taken to convert it to bacpac

Hi, sorry I'm not sure what you were suggesting could significantly reduce the time?

Anurag Sharma 17,606 Reputation points

2020-11-23T03:56:40.617+00:00

Hi @Nigel Morse , sorry for the confusion. What I meant to say was if instead of using bacpac if we could have directly used the native backups, it would have reduced the time. However with current limitation we cannot use the native backups.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Restore Azure Managed Instance to Azure SQL Database

1 answer

Your answer