Backup for Azure sql DB to Azure blob storage

Question

Hello Team,

I need to take a backup of azure sql database and copy the backup file directly to azure blob storage.

I don't have space to copy 500GB database backup file in a local Server . So i need to copy the file directly to azure blob storage.

Please let me know the suitable document for it.

Regards

Rohit

Answer 1

Hi Rohit,

I'm assuming you want .bacpac file of a Azure SQL DB. Follow below steps.

1. If you have SSMS installed in your machine, connect to database using ssms, use the server credentials or get access to take backup.
2. Go to database, right click -> Task ->Export Data tier application ->click on "Save to Microsoft Azure" click on connect, get authenticated..
3. Select storage account, container and file name, click next and then Export.

1. Backup will be saved in the blob storage.

If the above solution doesn't work, you can shrink the database and then take backup, this query reduces the size.

Answer 2

This page will help you with steps to backup:

https://azurelessons.com/backup-azure-sql-database/

And this is a guide for automatic backup:

https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?view=azuresql-db

Answer 3

Hi
Rohit Kulkarni •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you want to backup Azure SQL Database to Blob Storage of size 500GB.

To export a database using the Azure portal, open the page for your database and select Export on the toolbar.

If you are exporting to blob storage, the maximum size of a BACPAC file is 200 GB. To archive a larger BACPAC file, export to local storage with SqlPackage.

We recommend the use of the SQLPackage utility for scale and performance in most production environments. You can run multiple SqlPackage commands in parallel for subsets of tables to speed up import/export operations.

To export a database in SQL Database using the SQLPackage command-line utility, see Export parameters and properties. The SQLPackage utility is available for Windows, macOS, and Linux.

This example shows how to export a database using SqlPackage with Active Directory Universal Authentication:

SqlPackage /a:Export /tf:testExport.BACPAC /scs:"Data Source=apptestserver.database.windows.net;Initial Catalog=MyDB;" /ua:True /tid:"apptest.onmicrosoft.com"

Reference Link: https://learn.microsoft.com/en-us/azure/azure-sql/database/database-export?view=azuresql-mi

Hope this helps. Please let us know if you could perform like above or if face any issue.

If not do let us know so that we can help you. Thanks

Answer 4

Hi @Rohit Kulkarni

As you mentioned you have 500 GB data to export so you need to export using SQLPackage only.

Here is a detailed blog about SQLPackage that explains how to achieve your requirement. Also you mentioned, you don't have enough space on your local machine for exporting to blob storage, you can temporarily spin up a Azure SQL VM with enough storage and use it for the export. Once the operation is completed, you can drop that VM.

Using SQLPackage to import or export SQL Server and Azure SQL DB - Microsoft Community Hub

Some reference document-

For larger databases, you may want to optimize the operation using properties available in this article and tips in Troubleshooting with SqlPackage.

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export?view=sql-server-ver16#command-line-syntax

Hope this helps!

Thank you.