Migrating or exporting a database from azure to SQL Server 2016

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?

enter image description here

Answer 1

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.

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

• SqlWorldWide: That will only do data, how about schema?
• Daniel Björk: It will do the whole database.
• Daniel Björk: You can read more about it here: learn.microsoft.com/en-us/sql/relational-databases/…
• SqlWorldWide: Thank you. I hit extract instead of export and only saw option for dacpac and not bacpac. Sorry about that.
• Daniel Björk: No harm done. :)

Answer 2

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.

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

• Daniel Björk: I updated the answer with your more detailed description.
• Ramakant Dadhichi: Awesome! I am sure this will help others in future.

Answer 3

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 Server130円\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

Answer 4

Besides export\import .bacpac described by other users, there is another way to migrate a database, which in many cases may be preferable.

You can create a .sql script of Azure database and then run it in SQL Server

Generate t-sql script via Sql Server Managment Studio

right click on database -> Generate script

enter image description here

The Script Wizard will open.

In the Set Scripting Options section, click the Advanced. Specify the Types of data for script option to Schema and Data. Pay attention to the options Script for server version and Script for database engine type, here you can choose which database to create the script for.

enter image description here

Specify where the script should be saved and click Finish

To import a .sql file, simply open it in Sql Server Managment Studio and press F5. If the .sql file is very large then you can use the sqlcmd utility.

sqlcmd -S <server> -i C:\<your file here>.sql 

Note, when creating a .sql script or Export data tier application, the data should not be changed in the database, since these methods do not provide transactional consistency of the exported data. If you are exporting a live database, then create a copy of it temporarily for export.

CREATE DATABASE AdventureWorksCopy
AS COPY OF AdventureWorks
GO