Most of the peoples now looking for how to migrate the DB from on-premises to Azure and for this subjects, Microsoft provided us multiple solutions and services can be used for this request and for more information you can check my post (How to Migrate Database to Azure) that l already explained all of these services on it.But the question now can we take a backup from Azure SQL DB and restore it on our local SQL Server on-premises?
Answer yes but not with a normal process we know it backup and restore to know more information about azure backup check my post (Backup Tips on SQL Azure) we will use bacpac file using the Export option in Azure portal
To check my previous posts / articles on azure technology follow up this link
- How to Export Azure SQL Database.
- How to check Blob Files on Azure storage account
- How to Copy the bacpac file.
- How to import bacpac file into SQL Server
- Notes and considerations
- Resources and references
How to Export Azure SQL Database
- Using Azure portal open the Azure SQL Database and in the top, you will find Export option click on it and complete the steps, one of the steps you should have storage account to save this bacpac file on it
- Using SQLPackage utility: for more information about how to do it check Microsoft official articles
To Monitor the Export progress From Azure portal under the Azure SQL DB server on the left side, you will find Import/Export History.
How to check Blob Files on Azure storage account
- Using Azure Portal: Search by (Storage Explorer (preview)) and from this services you can browse all of your files under your container as below
- Using Storage Explorer tool: this is Microsoft tool you can download it from HERE and install it on your local servers or your PC and from it, you can browse all of the things related to your storage accounts for more information about Managing and implementing Azure storage check my post (Managing and implementing Azure storage)
How to Copy the bacpac file
After the export and now we have bacpac file on our storage account we need to copy it to our local machine we have 3 options here
- Azure port Storage Explorer (preview): using this services you can browse your blob containers and do download for bacpac file.
- Using Storage Explorer tool: you can use this tool and browse your blob containers and do download for bacpac file , even this tool it can provide you AzCopy command
- Microsoft Azure Storage Azcopy tool: it is Microsoft tool you can download it from here and install it on your PCthen you can manage the azure storage using azcopy command from it.
After download and installation, you should connect to azure account first using command azcopy login and it will direct you login using generated code as below, If your azure user account not added as an external user in the tenant you will not be able to login so first you should add your account as external use in the tenant
How to import bacpac file into SQL Server
On SQL Server Management Studio we can do this process using option Import Data-tier Application, after two or three steps Azure SQL Database will be deployed on your SQL Server on-Premises.
Notes and considerations
- The maximum size of a BACPAC file archived to Azure Blob storage is 200 GB.
- Archiving to Azure premium storage by using a BACPAC file is not supported.
- If the export operation exceeds 20 hours, it may be canceled.
- You must be sure that no write activity is occurring during the export.
- You can use normal Import / Export option in SQL Server Management Studio for moving data from Azure to SQL Server.