How to export data from Azure SQL to local dev database

Tommi Laukkanen

• 2 min read

You might have had a situation where you'd like to replicate a specific functionality locally that you have in your application running in Azure with same Azure SQL Database state. This tutorial explains how you can use Azure Data Studio and SQL Server Dacpac extension to export and import your data and schema from Azure SQL to local dev machine.

Grant access in Azure SQL Server Firewall

There are many options for exporting data but one of the easiest is to use free Azure Data Studio to both export and import data. First, you'll need to enable connection from your local machine to Azure SQL Server. You can do this from Azure Portal:

  • Navigate to your Azure SQL Database
  • Navigate to Firewall rules
Update: Portal keeps updating so firewall might also be accessed via Network panel
  • Add your local machine's public IP address as new rule
You can check your IP address using service like https://whatismyip.com

Note: For good security - remember to remove this rule when you don't need the access anymore

Now you should be able to connect to your Azure SQL database with Azure Data Studio. You can check your connection string from the Azure SQL Server overview page.

Disclaimer note: Your Azure setup might have other firewall rules that could prevent from you from connecting directly to your SQL Server so this might not be suitable for all setups - especially more hardened production environments.

Exporting and importing data and schema

We can't use normal backup/restore feature as that's not supported by Azure SQL Server (as of 2022) but we can do the following:

Install SQL Server Dacpac extension to Azure Data Studio - this can be done easiest from Data Studio's "Extensions" view. After extension is installed:

  • Export: click "Data-tier Application wizard" for your Azure SQL DB and select the "Export Bacpac" option
  • Import: When export finished then import to your local database with "Import Bacpac" option
Export and import options in Dacpac extension's Data-tier Application Wizard

Done - now we have your data in your local database and we are ready to debug our app with the same data.

Mastodon