Question :
I am playing around with SQL Azure and in my learning curve. I Have migrated a database from my on premises instance to the Azure server. Now my question is, how do I move a database from Azure to on premises instance.
Source : Azure , Database name : Azuretest
Destination : Sql server 2016 on premises instance in my laptop.
Is Azure to On Premise database migration possible?
Answer :
- Export the Data-tier to local disk from the Azure database. It will save a bacpac file.
Right click the database you need to export –> Task–>Export
data-tier application and follow the steps until the bacpac file is
created.
- Import the backpack file from the local disk to the On premises Instance by using the same utility from tasks.
Right click the database branch in your on premise instance –> Import
data tier application and now use the bacpac file saved in first step
to create the database.
Just in case someone needs more details on the steps as mentioned by Daniel:
- Export the Data-tier to local disk from the Azure database. It will save a bacpac file.
Right click the database you need to export –> Task–>Export
data-tier application and follow the steps until the bacpac file is
created.
- Import the backpack file from the local disk to the On premises Instance by using the same utility from tasks.
Right click the database branch in your on premise instance –> Import
data tier application and now use the bacpac file saved in first step
to create the database.
Thank you Daniel.
Creating an empty database in Azure.
USE [master]
GO
CREATE DATABASE [Migrate2OnPrem]
GO
ALTER DATABASE [Migrate2OnPrem] SET COMPATIBILITY_LEVEL = 130
GO
ALTER DATABASE [Migrate2OnPrem] SET QUERY_STORE (OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 7), DATA_FLUSH_INTERVAL_SECONDS = 900,
INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 10, QUERY_CAPTURE_MODE = AUTO,
SIZE_BASED_CLEANUP_MODE = AUTO)
GO
ALTER DATABASE [Migrate2OnPrem] SET READ_WRITE GO
Exporting database to a .bacpac file
Using sqlpackage.exe action Export
create a .bacpac
file at my local host.
cd "C:Program FilesMicrosoft SQL Server130DACbin"