Oct 2021 - Copy an Azure SQL Database to dev computer


There are several blog posts on this site answering how to copy an Azure SQL database. Since they are older or in some cases lost (Wayne's Microsoft blog posts are gone), I'll link to the Microsoft docs. 

Enterprise data or functionality?

Don't use these processes if you need change detection or other enterprise-level requirements. Copy the Azure SQL database and keep it in the cloud.

Make a copy of your Azure Cloud database

To copy an existing Azure SQL Database in the Azure portal, you can copy or export. Generally, I choose to export to Azure Storage Blob container as a bacpac file. This allows me to use it as either a cloud or local database.

The bacpac file includes data and schema.

Watch for export completion

To watch for export completion, from the Azure Server (not database) in the portal, use the  Import/Export History in the Data Management section.

Download bacpac file from Azure Storage

To download the bacpac file, Azure Storage container in the Azure portal. Select the bacpac file then download it to your local computer.

Restore SQL database to local machine from bacpac file

Using SSMS, import a data-tier application. Select the downloaded bacpac file. 

Because the Azure SQL db is a contained db, you need to change the master db on your local server with the following t-sql executed against the master db.

exec sp_Configure 'contained database authentication', 1

go 

reconfigure 


@dfberry




Comments

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

How to convert SVG data to a Png Image file Using InkScape