Backup azure-sql-database

Question

I am currently trying to write a script to create a backup of an Azure SQL database and store it in azure blob storage. While doing some research, found out may possible ways but as I can see none of them are working in Azure. Can someone give me a clarification on this and some useful documents. Thank you.

Answer 1

Hello Dinuka Wimalarathna

Yes, there are several ways to create a backup of an Azure SQL database and store it in Azure Blob Storage. The exact method you choose will depend on your specific requirements and the tools you have available. Here are some options to consider:

Use the Azure portal or Azure PowerShell to create a storage account and container in Azure Blob Storage, then use the built-in backup and restore feature in Azure SQL Database to create a backup and store it in the container.

Use Azure Automation to schedule a PowerShell script that creates a backup of the database and uploads it to a storage account in Azure Blob Storage.

Use Azure Data Factory to create a pipeline that copies the database to a storage account in Azure Blob Storage.

Use Azure Logic Apps to create a workflow that creates a backup of the database and uploads it to a storage account in Azure Blob Storage.

Here are some useful documents that can help you get started:

Back up an Azure SQL database: https://docs.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview

Store SQL database backups in Azure Blob Storage: https://docs.microsoft.com/en-us/azure/azure-sql/database/backup-restore-storage-azure-blob-storage

Create a storage account: https://docs.microsoft.com/en-us/azure/storage/common/storage-account-create?tabs=azure-portal

Create a container in Azure Blob Storage: https://docs.microsoft.com/en-us/azure/storage/blobs/storage-quickstart-blobs-portal#create-a-container

Use Azure Automation to schedule a backup: https://docs.microsoft.com/en-us/azure/automation/automation-solution-sql-server-backup

Use Azure Data Factory to copy data to Blob Storage: https://docs.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal

Use Azure Logic Apps to create workflows: https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-overview

I hope this helps!

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Hi Dinuka Wimalarathna •,

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

As I understand from the question, you are currently trying to write a script to create a backup of an Azure SQL database and store it in azure blob storage. You are unable to perform it and want supporting documents to do so.

In Azure SQL Database, backups are managed internally by Azure.

Azure SQL Database creates:

• Full backups every week.
• Differential backups every 12 or 24 hours.
• Transaction log backups approximately every 10 minutes.

However, in your question you want to create backup and store it in Blob. For this purpose, when you need to export a database for archiving or for moving to another platform, you can export the database schema and data to a BACPAC file. A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from the database. A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database.

Note:

BACPACs are not intended to be used for backup and restore operations. Azure automatically creates backups for every user database. For details, see business continuity overview and SQL Database backups.

Considerations

For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export, or that you are exporting from a transactionally consistent copy of your database.

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.

Exporting a BACPAC file to Azure premium storage using the methods discussed in this article is not supported.

Storage behind a firewall is currently not supported.

Immutable storage is currently not supported.

Storage file name or the input value for StorageURI should be fewer than 128 characters long and cannot end with '.' and cannot contain special characters like a space character or '<,>,*,%,&,:,,/,?'.

If the export operation exceeds 20 hours, it may be canceled. To increase performance during export, you can:

• Temporarily increase your compute size.
• Cease all read and write activity during the export.
• Use a clustered index with non-null values on all large tables. Without clustered indexes, an export may fail if it takes longer than 6-12 hours. This is because the export service needs to complete a table scan to try to export entire table. A good way to determine if your tables are optimized for export is to run DBCC SHOW_STATISTICS and make sure that the RANGE_HI_KEY is not null and its value has good distribution. For details, see DBCC SHOW_STATISTICS.

Azure SQL Managed Instance does not currently support exporting a database to a BACPAC file using the Azure portal or Azure PowerShell. To export a managed instance into a BACPAC file, use SQL Server Management Studio (SSMS) or SQLPackage.

• For larger databases, BACPAC export/import may take a long time, and may fail for various reasons.

Steps to create BACPAC:

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

2).Specify the BACPAC filename, select an existing Azure storage account and container for the export, and then provide the appropriate credentials for access to the source database. A SQL Server admin login is needed here even if you are the Azure admin, as being an Azure admin does not equate to having admin permissions in Azure SQL Database.

Reference document link: Export a Database to a BACPAC file.

Please let us know if you face any issue in performing the steps of Export or if you have any further query do share with us.

Thank you.