Restore a .bak file from azure blob storage to azure dedicated sql poool

Question

Restore a .bak file from azure blob storage to azure dedicated sql poool

Seenuvasan, Venkatanathan 60

I am currently using Azure dedicated SQL pool (Azure Data warehouse). The requirement is, have to restore the .bak file from azure blob storage to azure sql server. This should be automated.

As far as i know, backup and restore option is unavailable in azure dedicated SQL pool. what is the best one I need to choose from the below resource to accomplish the requirement.

Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics

Note : I don't want to use any azure VM.

Please guide me the best way to do this.

ShaktiSingh-MSFT 16,276 Reputation points

2023-08-11T12:36:49.95+00:00

Hi
Seenuvasan, Venkatanathan •,

Following up to see if the below suggestion was helpful. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Accepted answer

0 additional answers

Your answer

ShaktiSingh-MSFT 16,276 Reputation points

2023-08-11T12:36:49.95+00:00

Hi
Seenuvasan, Venkatanathan •,

Following up to see if the below suggestion was helpful. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Answer 1

Nandan Hegde 34,941 MVP

Hey,

Can you please confirm what is the .bak file created from?
is it a SQL server?

Because you cannot restore .bak file in Azure Synapse as Azure Synapse is an MPP (OLAP) unlike other MSFT databases.

What is the use case/downstreams of the new database?
Depending on that we can split our thoughts between Azure SQL DB or SQL MI.

SQL MI is a costly affair unlike Azure SQL DB but is a case of lift and shift scenario.

https://www.mssqltips.com/sqlservertip/7718/azure-sql-managed-instance-vs-azure-sql-database/

Seenuvasan, Venkatanathan 60 Reputation points

2023-08-10T09:58:06.5033333+00:00

Hi Hegde,

Thanks for your response.

Yes, It is sql server backup file. this backup file will be landed into azure blob storage weekly once. Afterwards, we need restore this into our azure sql server as a database and ingest few tables from this database for downstream purpose.
Nandan Hegde 34,941 Reputation points MVP

2023-08-10T10:21:26.49+00:00

Hey,

Based on your requirement, it seems Azure SQL database is the correct use case for you rather SQL MI.

but why to create a database every time via backup?

You can use a tool called Azure data factory to sync the specifc tables from your SQL server to a dedicated Azure SQL database on scheduled basis :

https://learn.microsoft.com/en-us/azure/architecture/data-science-process/move-sql-azure-adf

or use Azure SQL DB data sync feature to sync the data from on prem to Azure SQL DB
https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql
Seenuvasan, Venkatanathan 60 Reputation points

2023-08-10T10:35:04.21+00:00

Hi Hegde,

Thanks. We use the Azure data factory but unfortunately my upstream doesn't allow to ingest the tables directly from their sql server.

Is it possible to achieve this through azure sql db ? becasue, I tried but I don't see any option to restore the .bak file from azure blob storage. I searched in web. it seems most of articles talks about how to do this inside the VM. But I don't want to use VM.
Nandan Hegde 34,941 Reputation points MVP

2023-08-10T13:49:47.1133333+00:00

ohh!.ok
Can the upstream share bacpac file rather than .bak?
That would be most preferable.

There is no direct way to restore .bak in Azure SQL DB. Please follow the below MSFT doc :

https://devblogs.microsoft.com/azure-sql/using-an-azure-container-instance-to-convert-a-bak-to-bacpac-for-import-into-azure-sql-database/?ref=morioh.com&utm_source=morioh.com

to convert from .bak to bacpac

Share via

Restore a .bak file from azure blob storage to azure dedicated sql poool

0 additional answers

Your answer