Working with Azure SQL Database on elastic pool I often need to pull entire database locally mainly for performance reasons on intensive tasks. Currently, whenever I need to bring a cloud database to my local (Sql Server on Docker) I do the following: Run this stored procedure to sync the metadata (otherwise the export fails ) Export the database as a bacpac to a blob storage Download the bacpac from the storage with Azure Data Explorer Import the bacpac using Azure with Azure Data Studio Since I need to perform this operation often, what is the fastest way to get a database from Azure Sql Server to a local Sql Server instance, a docker container in my case?

Quickest way to restore remote Azure SQL Database from the cloud to local

TheSimoz 0

Working with Azure SQL Database on elastic pool I often need to pull entire database locally mainly for performance reasons on intensive tasks.

Currently, whenever I need to bring a cloud database to my local (Sql Server on Docker) I do the following:

Run this stored procedure to sync the metadata (otherwise the export fails )
Export the database as a bacpac to a blob storage
Download the bacpac from the storage with Azure Data Explorer
Import the bacpac using Azure with Azure Data Studio

Since I need to perform this operation often, what is the fastest way to get a database from Azure Sql Server to a local Sql Server instance, a docker container in my case?

Sai Raghunadh M 150 Reputation points Microsoft Vendor

2024-09-03T13:14:57.6133333+00:00
Hi @ TheSimoz,Thanks for the question and using MS Q&A platform.

Here are the steps to restore a remote Azure SQL Database from the cloud to a local SQL Server instance using the SQL Server Management Studio (SSMS) Restore Database wizard:

Connect to the remote Azure SQL Database using SSMS.

Right-click on the database you want to restore and select "Tasks" > "Export Data-tier Application".

Follow the wizard to export the database as a bacpac file to a local file system or Azure Blob storage.

Connect to your local SQL Server instance using SSMS.

Right-click on the "Databases" node and select "Restore Database".

Follow the wizard to restore the database from the bacpac file you exported in step 3.

It's important to note that the restore process may take some time depending on the size of the database and the network bandwidth between the remote Azure SQL Database and your local SQL Server instance. Also, restoring a database from the cloud to a local SQL Server instance may not be the best approach for all scenarios. If you need to work with the data in the remote Azure SQL Database frequently, you may want to consider using a different approach such as connecting to the remote database directly or using Azure Data Sync to keep the data in sync between the remote and local databases.

I hope this information helps, please do let us know if you have any Queries.
TheSimoz 0 Reputation points

2024-09-03T14:09:49.8466667+00:00

Thanks, I was looking for a way that takes less time or at least less steps.
It seems that you described what I mentioned in the question but with SSMS instead of Az Data studio.
Sai Raghunadh M 150 Reputation points Microsoft Vendor

2024-09-04T12:00:25.6033333+00:00

Hi @ TheSimoz,

Could you please refer the below document which might help you.

https://www.mssqltips.com/sqlservertip/6004/restore-azure-sql-database-to-local-sql-server/
Sai Raghunadh M 150 Reputation points Microsoft Vendor

2024-09-05T12:04:35.18+00:00

Hi @ TheSimoz,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Erland Sommarskog 110.3K Reputation points

2024-09-05T19:12:16.39+00:00

Well, given how off-the-mark your posts have been it is maybe not surprising TheSimonz gave up. He asked how to perform the process faster, and you have only told him how to do it all - which he already knows.

Myself, I've been thinking "PowerShell", but I don't know what cmdlets there may be available. I had a look at dbatools.io, but I could find anything.
Greg Low 1,760 Reputation points Microsoft Regional Director

2024-09-06T06:26:08.3533333+00:00

The other option to consider is scaling within Azure. We regularly have DBs at low DTUs, scale them up for intensive work, and then back down again. Might be easier than to/from on-premises. You do need to pick options that scale quickly though. For example, from S2 anywhere up to S12 is fast (usually about 1 minute). But S0 or S1 is glacial as they involve DB copies.
Sai Raghunadh M 150 Reputation points Microsoft Vendor

2024-09-09T08:05:45.2933333+00:00

Hi @ TheSimoz,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Quickest way to restore remote Azure SQL Database from the cloud to local

Your answer