Database user-initiated backups

Azure SQL Managed Instance is a fully managed PaaS version of SQL Server Database Engine hosted in Azure cloud. It exposes entire SQL Server instance, supports almost all features available in SQL Server 2017 and combine this with instant scaling and intelligence that is available in Azure PaaS service.

Managed Instance will support native BACKUP like SQL Server where you can put backups to URL/Azure Blob Storage. In this post you will see how to backup database to Azure Blob Storage using Managed Instance.

Access to Azure Blob Storage

First, you would need to store credentials that would be used to access Azure Blob Storage:

CREATE CREDENTIAL [https://myacc.blob.core.windows.net/testcontainer] 
 WITH IDENTITY='SHARED ACCESS SIGNATURE'
 , SECRET = 'sv=2014-02-14&sr=c&sig=GV0T9y%2B9%2F9%2FLEIipmuVee3jvYj4WpvBblo%3D&se=2019-06-01T00%2A%3AZ&sp=rwdl';

The name of the credential should be the same as URL of target Azure Blob Storage container. In this case, this is myacc account and testcontainer container stored on the URL: https://myacc.blob.core.windows.net/testcontainer

When you put the secret in credential make sure that you DO NOT put ‘?’ as first character of secret. This is one of the most common mistake that people are making, which causes BACKUP to fail.

COPY_ONLY Backup

Once you create a credential, you can backup any database using standard BACKUP T-SQL command:

BACKUP DATABASE tpcc2501
 TO URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501.bak'
 WITH COPY_ONLY

The only mandatory property is COPY_ONLY because Azure SQL Managed Instance owns the backup chain of the database. This means that you cannot perform database backup that breaks the backup chain. Azure SQL Managed Instance allows you to perform only COPY_ONLY backups.

Stripping backup

Azure Blob Storage block blobs have 200GB size limitation. If your database is bigger than 200GB, you should use stripped backup by providing several URL locations where parts of the backup will be placed:

BACKUP DATABASE tpcc2501
 TO URL = 'https://myacc.blob.core.windows.net/testcontainer/00-tpcc2501.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/01-tpcc2501.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/02-tpcc2501.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/03-tpcc2501.bak'
 WITH COPY_ONLY

This way, every stripe will contain a part of the backup and you will not reach the 200GB limit. You can divide the size of your database with 200GB to find out how many stripes you would need.

It might be good to put different 4-char prefixes in in backup file name because your backups and restores might be faster.

MAXTRANSFERSIZE

If you have a big database, it would be good to specify MAXTRANSFERSIZE=4194304 option:

BACKUP DATABASE tpcc2501
 TO URL =  TO URL = 'https://myacc.blob.core.windows.net/testcontainer/00-tpcc2501.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/01-tpcc2501.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/02-tpcc2501.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/03-tpcc2501.bak'
 WITH COPY_ONLY, MAXTRANSFERSIZE = 4194304, COMPRESSION

Dimitri Furman explained why is this option important to backup very large databases.

Also, a good idea would be to use COMPRESSION option to decrease bandwidth between Managed Instance and Azure Blob storage.

You also can specify additional options in the WITH clause such as CHECKSUM or STATS = <number>. Backups can be performed across Azure regions, but it would be good to check are the database and storage account placed within the same region to speedup the backup.