Taking the backup of Azure SQL Managed Instance databases is not exactly similar to how the backups are taken on in-house SQL Server instances. And, similar ways, restores are also not exactly equivalent to the on-premise restores.
Let’s how they differ and what is T-SQL support to automate them.
By default, all databases created on Azure including Managed Instances are encrypted by TDE (Transparent Data Encryption) and you are not allowed to take a manual backup of it though you can restore a copy of the database through the Azure portal. However, if you disable the TDE then you can take the backup of it. Although not recommended but to satisfy the need of taking the backups and restoring somewhere else, disabling the TDE is a mandatory requirement. However, you can enable the TDE back as soon as backup has been taken but keep in mind the time to disable/enable the TDE which largely depends on the size of the database. You can refer to other posts (How to Disable TDE) to see how to disable/enable the TDE.
Let’s now see how exactly you can take backup and restore using T-SQL
Backups –
BACKUP DATABASE <database name>
TO URL = 'https://<storage account name>.blob.core.windows.net/<container>/<database name>.bak'
WITH COPY_ONLY, COMPRESSION, BLOCKSIZE = 65536, CHECKSUM
As you see that backup is stored on Azure Blob Storage URL so in order for your Managed Instance to access this location, you will have to first create a credential also as follows –
CREATE CREDENTIAL [https://<storage accout name>.blob.core.windows.net/<container name>]
-- this name must match the container path, start with https and must not contain a forward slash at the end
WITH IDENTITY='SHARED ACCESS SIGNATURE'
, SECRET = '<Shared Access Signature>'
So, once you have disabled the TDE and created the credential, you must be able to take the backup using the above command.
If the databases are quite large, approximately greater than 185GB, backup needs to be converted into stripes as single blob storage can’t hold too long files. So, you can use the following command to take a striped backup –
BACKUP DATABASE <database name>
TO URL = 'https://<storage account name>.blob.core.windows.net/<container>/<database name>-1.bak'
, URL = 'https://<storage account name>.blob.core.windows.net/<container>/<database name>-2.bak'
WITH COPY_ONLY, COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM
Restores –
Azure SQL Managed Instance doesn’t allow you to restore a database by overwriting it. So, if you want to restore a database on your own, you first need to delete the database and then restore it. Do not forget to script out the security or any other thing you would like to implement back on the restored database.
RESTORE DATABASE <database name> FROM
URL = 'https://<storage account name>.blob.core.windows.net/<container>/<database name>.bak'
RESTORE DATABASE <database name> FROM
URL = 'https://<storage account name>.blob.core.windows.net/<container>/<database name>-1.bak'
, URL = 'https://<storage account name>.blob.core.windows.net/<container>/<database name>-2.bak'