How to Fetch an .bak file from ADF pipeline?

Ayush Chauhan 116

I want to migrate the data in the .bak file in my storage account to SQL Db, through ADF. As the .bak file is not a supported file format in ADF datasets, then what's the workaround that I should opt to get it done?

MartinJaffer-MSFT 26,021 Reputation points

2021-07-21T20:27:27.817+00:00

Hello @Ayush Chauhan and welcome to Microsoft Q&A.

Any file/blob can be copied or moved to another file/blob location, regardless of file type, by using the Binary datasets. What makes this possible, and different, from other dataset types, is that the data is just being copied as-is, without any transformation, reading, or ingestion.

To my understanding, .bak files are used by SQL to overwrite and restore a database to a point in time. This is something initiated by the database, and not handled by Data Factory.

If you want to simply migrate or ingest data, I recommend using a method other than .bak files.

That is my understanding, at least. Please let me know if I got something wrong.

So, which are your trying to do? Restore a database? Migrate entire database from on-prem to cloud? Copy data?
MartinJaffer-MSFT 26,021 Reputation points

2021-08-11T20:09:51.61+00:00

If our discussion resolved the issue, please mark as accepted answer.
Laxmi Kakarlapudi 21 Reputation points

2021-10-21T23:17:45.477+00:00

Hi Ayush,

How are you able to get .bak files into datalake. I used azure webactivity to get the url and once I put the url in http it downloads the .bak file. But I am stuck how to pass the url and get .bak file to adls2? Can you explain your pipeline?

Thanks,
Laxmi

1 answer

Ayush Chauhan 116 Reputation points

2021-07-22T05:43:09.653+00:00

Hello @MartinJaffer-MSFT

Azure has deprecated the online migration to Azure SQL Db, that's why we are trying to simply store full backup from SQL Server to Azure storage account and then try to migrate data with .bak files in storage account to Azure SQL Db through ADF.

Other than that in the case of the binary dataset, we have to choose a binary dataset in both sink and source, but Azure SQL Db acts as the sink in our case.
Please sign in to rate this answer.
MartinJaffer-MSFT 26,021 Reputation points

2021-07-28T22:03:41.107+00:00

Okay, so if I understand correctly you are going:

On prem SQL server -> Azure storage -> Azure SQL

I'm sorry, @Ayush Chauhan , but I don't think this precise combination of technologies will work that way.

While Data Factory can be used as a data migration method, Data Factory does not work directly with .bak files. Data Factory instead would connect to both databases, and pull the data. There are a number of implementations Data Factory can do, but at the most basic level, each table is a separate write action.

More Migration tools
Other migration options

For .bak file specifically, it can be retrieved from Azure Storage for SQL server, and Azure SQL Managed Instance.
Sign in to comment