How to back up Azure SQL DB into BAK file?

Question

How to back up Azure SQL DB into BAK file?

Justin Doh 880

I am trying to create a Bak file format from Azure SQL DB into Azure Storage (like Blob storage) on a daily basis for an external client.

Previously, I had an external client to log into that Azure SQL DB to perform query, but it appears that it is too slow.

So, our team decided to have an external client have an access to BAK file so they can get the file and restore into their SQL DB.

I realized that Azure SQL DB backs up into Bacpac file format, not Bak format.

I am using Azure Data Factory to move data, and I am not sure ADF can be used to do the task.

Please let me know the best practice to generate Bak file from Azure SQL DB and push that file into Azure Blob storage.

Thanks.

Justin Doh 880 Reputation points

2025-12-12T19:52:08.5833333+00:00

I got a different requirement now. I can use On-Prem SQL instead of Azure SQL. How do I schedule to create a BAK from On-Prem SQL and send the BAK file to Azure Blob Storage?
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2025-12-12T20:15:20.3166667+00:00

You don't. Or, well, if all you want to do is to store a on-prem backup in Azure Blob Storage, you can use BACKUP TO URL to do this directly. But you cannot restore this backup into Azure SQL Database. To copy from on-prem SQL, you need to use BACPAC or some sync method to refresh the target database. Transactional replication is a possible option.
Pilladi Padma Sai Manisha 595 Reputation points Microsoft External Staff Moderator

2025-12-12T20:42:18.06+00:00
Hi Justin Doh,
Since you’re moving to On-Prem SQL Server, generating a .BAK and sending it to Azure Storage is much easier. The recommended setup is:

Create the BAK file on-prem

Use a SQL Server Agent job to run a daily backup, e.g.:

BACKUP DATABASE YourDB TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION;

Upload the BAK to Azure Blob

Most customers automate this using AzCopy or a small PowerShell script scheduled with Windows Task Scheduler.

AzCopy example:

azcopy copy "D:\Backups\YourDB.bak" "https://yourstorage.blob.core.windows.net/sqlbackups?<sas-token>"

Optional cleanup
Have a script delete old local files and old blobs to manage storage.

This setup gives you a reliable daily .BAK generation on-prem and pushes it automatically to Azure Blob for your client to download. If you want, I can share a ready-to-use script for the whole workflow.
Justin Doh 880 Reputation points

2025-12-12T22:11:23.67+00:00

Thank you!

What is SQL Script to delete previous bak file?
VRISHABHANATH PATIL 2,635 Reputation points Microsoft External Staff Moderator

2025-12-13T06:48:52.3233333+00:00

Hi @Justin Doh

Kindly refer the below reference link to delete the previous .bak file -

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-backuphistory-transact-sql?view=sql-server-ver17
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2025-12-13T09:36:29.9+00:00

Kindly refer the below reference link to delete the previous .bak file -

Eh, no. That procedure deletes the data about the backup stored in msdb. It does not delete any backup files, and this is also spelt out on the page you referred to:

The physical backup files are preserved, even if all the history is deleted.

Going back to Pilladi's post, I am a little curious why you suggest this two-step operation, rather than simply using BACKUP TO URL. But I have never worked with BACKUP TO URL myself, so maybe there is a catch?

With BACKUP TO URL, I expect that you can simply use WITH INIT which means that you over-write the current backup file (instead of appending to it.)

But again, if the aim is that this backup is to be restored into Azure SQL Database, that will not work out. You simply can't do that.
Justin Doh 880 Reputation points

2025-12-15T19:18:49.3133333+00:00

@Erland Sommarskog Thank you for your comment. What does it mean by "backup to URL"?

Also, regards to the SQL statement (below), it appears that it would just overwrite previous copy, so that I don't have to delete previous one, correct?

BACKUP DATABASE YourDB TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION;

I am not sure what you meant by "aim is that this backup is to be restored into Azure SQL Database..", my requirement has been changed so that I could back up into BAK file.

I am working on Powershell to AzCopy into Azure storage now.

Thanks.
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2025-12-15T22:08:40.4966667+00:00
I am not sure what you meant by "aim is that this backup is to be restored into Azure SQL Database..", my requirement has been changed so that I could back up into BAK file.

In your original post, you said

So, our team decided to have an external client have an access to BAK file so they can get the file and restore into their SQL DB.

I realise that this may not be on the table any more, but I was not sure, so I wanted to make that point, so you know what you are doing.

Also, regards to the SQL statement (below), it appears that it would just overwrite previous copy, so that I don't have to delete previous one, correct? BACKUP DATABASE YourDB TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION;

No, this will not overrwrite the existing backup, it will append to the existing backup file, so there are multiple backups in the same file. This can be useful, if you know what you are doing. But if you are not, and you have seven backups in the same file, and then say:

RESTORE DATABASE db FROM DISK = 'D:\Backups\YourDB.bak'

you will restore the oldest backup. To restore the most recent, you would need to say:

RESTORE DATABASE db FROM DISK = 'D:\Backups\YourDB.bak' WITH FILE = 7

There is more than one person who has walked into that trap.

@Erland Sommarskog Thank you for your comment. What does it mean by "backup to URL"?

You can say BACKUP db TO URL = 'http....'. I have not used it myself, but you can read more about it here: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url
Pilladi Padma Sai Manisha 595 Reputation points Microsoft External Staff Moderator

2025-12-15T22:36:15.9433333+00:00

Hi Justin Doh,
Just checking in did the solution we provided resolve your issue. Please reply if you have any questions in this matter and we will gladly continue the discussion.****
Justin Doh 880 Reputation points

2025-12-17T18:38:39.51+00:00

@Erland Sommarskog Regards to SQL statement:

BACKUP DATABASE YourDB TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION;

What does it mean that 'it will append to the existing backup file, so there are multiple backups in the same file'?

My job is provide a client the most recent bak file. Did this process complete that requirement?
Justin Doh 880 Reputation points

2025-12-17T22:55:17.8+00:00

@Erland Sommarskog How do I take your latest comment as Answer to this post?
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2025-12-17T22:58:31.95+00:00

I have converted it to an Answer, so that you can accept it.

Answer accepted by question author

3 additional answers

Your answer

Justin Doh 880 Reputation points

2025-12-12T19:52:08.5833333+00:00

I got a different requirement now. I can use On-Prem SQL instead of Azure SQL. How do I schedule to create a BAK from On-Prem SQL and send the BAK file to Azure Blob Storage?
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2025-12-12T20:15:20.3166667+00:00

You don't. Or, well, if all you want to do is to store a on-prem backup in Azure Blob Storage, you can use BACKUP TO URL to do this directly. But you cannot restore this backup into Azure SQL Database. To copy from on-prem SQL, you need to use BACPAC or some sync method to refresh the target database. Transactional replication is a possible option.
Pilladi Padma Sai Manisha 595 Reputation points Microsoft External Staff Moderator

2025-12-12T20:42:18.06+00:00

Hi Justin Doh,
Since you’re moving to On-Prem SQL Server, generating a .BAK and sending it to Azure Storage is much easier. The recommended setup is:

Create the BAK file on-prem

Use a SQL Server Agent job to run a daily backup, e.g.:

BACKUP DATABASE YourDB TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION;

Upload the BAK to Azure Blob

Most customers automate this using AzCopy or a small PowerShell script scheduled with Windows Task Scheduler.

AzCopy example:

azcopy copy "D:\Backups\YourDB.bak" "https://yourstorage.blob.core.windows.net/sqlbackups?<sas-token>"

Optional cleanup
Have a script delete old local files and old blobs to manage storage.

This setup gives you a reliable daily .BAK generation on-prem and pushes it automatically to Azure Blob for your client to download. If you want, I can share a ready-to-use script for the whole workflow.
Justin Doh 880 Reputation points

2025-12-12T22:11:23.67+00:00

Thank you!

What is SQL Script to delete previous bak file?
VRISHABHANATH PATIL 2,635 Reputation points Microsoft External Staff Moderator

2025-12-13T06:48:52.3233333+00:00

Hi @Justin Doh

Kindly refer the below reference link to delete the previous .bak file -

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-backuphistory-transact-sql?view=sql-server-ver17
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2025-12-13T09:36:29.9+00:00

Kindly refer the below reference link to delete the previous .bak file -

Eh, no. That procedure deletes the data about the backup stored in msdb. It does not delete any backup files, and this is also spelt out on the page you referred to:

The physical backup files are preserved, even if all the history is deleted.

Going back to Pilladi's post, I am a little curious why you suggest this two-step operation, rather than simply using BACKUP TO URL. But I have never worked with BACKUP TO URL myself, so maybe there is a catch?

With BACKUP TO URL, I expect that you can simply use WITH INIT which means that you over-write the current backup file (instead of appending to it.)

But again, if the aim is that this backup is to be restored into Azure SQL Database, that will not work out. You simply can't do that.
Justin Doh 880 Reputation points

2025-12-15T19:18:49.3133333+00:00

@Erland Sommarskog Thank you for your comment. What does it mean by "backup to URL"?

Also, regards to the SQL statement (below), it appears that it would just overwrite previous copy, so that I don't have to delete previous one, correct?

BACKUP DATABASE YourDB TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION;

I am not sure what you meant by "aim is that this backup is to be restored into Azure SQL Database..", my requirement has been changed so that I could back up into BAK file.

I am working on Powershell to AzCopy into Azure storage now.

Thanks.
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2025-12-15T22:08:40.4966667+00:00

I am not sure what you meant by "aim is that this backup is to be restored into Azure SQL Database..", my requirement has been changed so that I could back up into BAK file.

In your original post, you said

So, our team decided to have an external client have an access to BAK file so they can get the file and restore into their SQL DB.

I realise that this may not be on the table any more, but I was not sure, so I wanted to make that point, so you know what you are doing.

Also, regards to the SQL statement (below), it appears that it would just overwrite previous copy, so that I don't have to delete previous one, correct? BACKUP DATABASE YourDB TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION;

No, this will not overrwrite the existing backup, it will append to the existing backup file, so there are multiple backups in the same file. This can be useful, if you know what you are doing. But if you are not, and you have seven backups in the same file, and then say:

RESTORE DATABASE db FROM DISK = 'D:\Backups\YourDB.bak'

you will restore the oldest backup. To restore the most recent, you would need to say:

RESTORE DATABASE db FROM DISK = 'D:\Backups\YourDB.bak' WITH FILE = 7

There is more than one person who has walked into that trap.

@Erland Sommarskog Thank you for your comment. What does it mean by "backup to URL"?

You can say BACKUP db TO URL = 'http....'. I have not used it myself, but you can read more about it here: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url
Pilladi Padma Sai Manisha 595 Reputation points Microsoft External Staff Moderator

2025-12-15T22:36:15.9433333+00:00

Hi Justin Doh,
Just checking in did the solution we provided resolve your issue. Please reply if you have any questions in this matter and we will gladly continue the discussion.****
Justin Doh 880 Reputation points

2025-12-17T18:38:39.51+00:00

@Erland Sommarskog Regards to SQL statement:

BACKUP DATABASE YourDB TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION;

What does it mean that 'it will append to the existing backup file, so there are multiple backups in the same file'?

My job is provide a client the most recent bak file. Did this process complete that requirement?
Justin Doh 880 Reputation points

2025-12-17T22:55:17.8+00:00

@Erland Sommarskog How do I take your latest comment as Answer to this post?
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2025-12-17T22:58:31.95+00:00

I have converted it to an Answer, so that you can accept it.

Answer 1

Erland Sommarskog 129.1K MVP Volunteer Moderator

What does it mean that 'it will append to the existing backup file, so there are multiple backups in the same file'?

Exactly.

You can avoid this by adding INIT:

BACKUP DATABASE YourDB TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION, INIT;

This will overwrite the existing file and delete the existing backup file.

Answer 2

Azure SQL Database does not support direct backup to a BAK file format. Instead, it uses BACPAC files for exporting databases. A BACPAC file is a ZIP file that contains the metadata and data from the database, and it can be stored in Azure Blob storage or locally.

To achieve your goal of creating a backup file for your external client, you can follow these steps:

Export the Database to a BACPAC File: You can use SQL Server Management Studio (SSMS), Azure Data Studio, or the SQLPackage utility to export your Azure SQL Database to a BACPAC file. This file can then be stored in Azure Blob storage.
- For example, using SSMS, you can right-click on the database, select Tasks, and then Export Data-tier Application... to create the BACPAC file.
Store the BACPAC File in Azure Blob Storage: During the export process, you can choose to save the BACPAC file directly to an Azure Blob storage container.
Automate the Process: If you want to automate this process on a daily basis, consider using Azure Data Factory (ADF) to orchestrate the export of the database to a BACPAC file and then move it to Azure Blob storage. While ADF does not directly create BAK files, it can facilitate the export of BACPAC files.
Client Access: Once the BACPAC file is in Azure Blob storage, your external client can access it for restoration into their SQL Database or SQL Server instance.

For more details on exporting to a BACPAC file and using Azure Blob storage, you can refer to the documentation provided by Microsoft.

Justin Doh 880 Reputation points

2025-12-11T23:18:11.4733333+00:00

What is process of converting BACPAC file into BAK file format? Can it be done using ADF?
VRISHABHANATH PATIL 2,635 Reputation points Microsoft External Staff Moderator

2025-12-12T02:43:04.32+00:00

Hi @Justin Doh

There is no direct way or Azure‑native service (including ADF) that can convert BACPAC into a BAK. The only supported process is Export BACPAC → Import into SQL Server → Back Up to BAK.

Answer 3

Hi Justin Doh,
Thankyou For Reaching Microsoft Q&A!
Azure SQL Database doesn't support native .BAK file backups like on-premises SQL Server these are exclusive to SQL Server instances or Azure SQL Managed Instance. Instead, it exports to BACPAC files, which include both schema and data and work well for your client's restore needs.

Manual BACPAC Export Steps:

Log into the Azure portal, navigate to your SQL server, select the database, and click Export on the Overview page. Choose your Blob storage account/container, provide a filename (like "DB-YYYYMMDD.bacpac"), and start the process max 200GB per file.

Daily Automation (Skip ADF):

Azure Data Factory handles data copying but not BACPAC exports, so use Azure Automation Runbook instead:

text
# PowerShell in Runbook
$exportUrl = "https://yourstorage.blob.core.windows.net/container/DB-$(Get-Date -f yyyyMMdd).bacpac?SAS_TOKEN"
New-AzSqlDatabaseExport -ResourceGroupName "your-rg" -ServerName "yourserver" -DatabaseName "yourdb" -StorageKeyType "StorageAccessKey" -StorageKey "yourkey" -StorageUri $exportUrl -AdministratorLogin "admin" -AdministratorLoginPassword (ConvertTo-SecureString "pass" -AsPlainText -Force)

Schedule daily, add retention cleanup (delete files >7 days).

Quick no-code alternative: Logic Apps with Recurrence trigger > "Export Database" action > save to Blob.

Client Access & SQLPackage Option:

Share a read-only SAS token for Blob access they restore via SSMS (Databases > Tasks > Import Data-tier Application). For large DBs or more control, download SQLPackage.exe and run exports locally before uploading:

text
sqlpackage.exe /Action:Export /SourceServerName:"server.database.windows.net" /SourceDatabaseName:"db" /SourceUser:"admin" /SourcePassword:"pass" /TargetFile:"output.bacpac"

BACPACs aren't full backups (use Azure's automated ones for PITR), but perfect for external sharing. Test restores end-to-end!

I hope this helps! If you have more questions or need further assistance, feel free to ask!

Answer 4

Erland Sommarskog 129.1K MVP Volunteer Moderator

As Pilladi points out, you cannot produce BAK files from Azure SQL Database.

There is however an alternative to BACPACs, and that is CREATE DATABASE AS COPY OF, which even can used to copy databases across tenants and subscriptions. Read further here: https://techcommunity.microsoft.com/blog/azuredbsupport/how-to-copy-azure-sql-database-to-a-different-subscription-and-different-tenant/3965985

Share via

How to back up Azure SQL DB into BAK file?

3 additional answers

Your answer