Share via

Azure SQL database backup

hoover-hash123 6 Reputation points
2021-01-12T20:43:35.453+00:00

Hello All,

I'm looking to try and automate database backups of our databases on our managed instance. Since these databases are on an Azure MI, from what I understand we cannot back these databases using the .BAK option (Tasks > Backup) and we must use the .BACPAC option (Tasks > Export Data-tier Application).

Is there a way we can automate the backup process and have the end result file uploaded to a storage container? Otherwise, how would you best approach obtaining database backups of Azure SQL databases?

I am aware of options within the Azure portal that we can configure such as retention policies per database, but it would be ideal for my company to have our backups in a container that's easily accessible for restoration and/or testing purposes.

Here's what I am doing today to get a database backup:

  1. Restore a copy of the database I wish to backup via the Azure portal
  2. Drop specific security users from the database that cause the bacpac process to fail. We have allowed Azure Active Directory authentication via Groups and these Groups must be dropped from the copied database in order for the process to succeed.
  3. Export the copied database
  4. Delete the copied database

Thanks! Any help or guidance would be greatly appreciated.

Azure SQL Database
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Navtej Singh Saini 4,226 Reputation points Microsoft Employee Moderator
    2021-01-14T05:10:31.05+00:00

    @xiongNWN-8368

    Depending on your need Copy only .bak backups are also possible. Please check these documents for the same:

    1. Document1
    2. Document2

    Also check the documents for the automation of the back up to the storage accounts"

    1. Document1
    2. Document 2

    Please check the documents and get back to us for any questions.

    Thanks
    Navtej S

    1 person found this answer helpful.
    0 comments
  2. Rohit Kumar 6 Reputation points
    2021-01-16T08:11:13.55+00:00

    Please note that you cannot backup Managed Instance manually to blob if you are using Azure Managed Encryption key. You will have to do following:

    1. Use Customer Managed Encryption Key
    2. You can only take copy-only full and tlog backups (no diff support yet)
    3. Whereever you are goign to restore this backup, that instance should also be using customer managed encryption key
    4. You cannot restore this backup over to on-prem or non-azure sql instances.

    You can easily automate this backup as you do for your on-prem using either agent or any 3rd party tool. We use Active-Batch to do it here, as well as use SSMS too.

    1 person found this answer helpful.
  3. Stacy Clark 31 Reputation points
    2021-10-11T05:00:26.107+00:00
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.