4

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?

enter image description here

4
  1. 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.

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

enter image description here

1

Just in case someone needs more details on the steps as mentioned by Daniel:

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

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

enter image description here

  • I updated the answer with your more detailed description. – Daniel Björk Aug 1 '17 at 19:34
  • 1
    Awesome! I am sure this will help others in future. – Ramakant Dadhichi Aug 1 '17 at 19:40
0

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 Files\Microsoft SQL Server\130\DAC\bin\"


.\SqlPackage.exe /Action:Export /SourceServerName:tcp:taiobadw.database.windows.net,1433  
 /SourceDatabaseName:Migrate2OnPrem /su:taiobmdjamshed /sp:password 
 /TargetFile:"C:\Migrate2OnPrem.bacpac" 

Importing to On-Prem server

Using sqlpackage.exe action Import import the .bacpac file from my local host (that you created in the step above.

.\sqlpackage.exe /a:Import /sf:C:\Migrate2OnPrem.bacpac /tsn:localhost /tdn:Migrate2OnPrem

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.