How automate backup and restore in Azure SQL Managed Instance?

Question

I'm the DBA of one Azure SQL Managed Instance and one of our clients asked us if is possible automate a backup and restore in order to have a copy of one of the database before one of our ETL procces start. That's because they want to do all insertions and work on the copy database instead of the main production database and after finish everything and if data validations are OK, change database name.

So I thought use a SQL Job just for backup a database to one blob storage and then restore using a backup file from a blob storage, the problem start when I realized that could not use a proxy account (related to the credential with access to blob storage)in the SQL Job and also there are some limitations in TSQL (WITH CREDENTIAL for example)

Now with those restrictions I don't know how can automate this task. If I am not wrong, ELASTIC JOBS are only for Azure SQLDatabase

Thanks in advance

Answer 1

You can use an SQL Agent Job to backup and restore a database from a storage account, you just need to create a credential in advance:

SELECT * FROM sys.credentials;
CREATE CREDENTIAL [https://StorageAccountName/ContainerName] 
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '/*SAS token*/'; 
GO
SELECT * FROM sys.credentials;

With the credential created you can then create a SQL job with two steps, one for backing up and another for restoring the backup:

/* Step 1 */
BACKUP DATABASE [DatabaseName] 
TO URL = N'https://StorageAccountName/ContainerName/BackupName.BAK' 
WITH COPY_ONLY;
/* Step 2 */
RESTORE DATABASE [CopyDatabaseName] 
FROM URL = N'https://StorageAccountName/ContainerName/BackupName.BAK';

On the network side, make sure the virtual network hosting the managed instance allows access to the storage account either trough a private or a service endpoint.

• Jonathan Fite: Note that if you are using TDE with MSFT key management then this will not work (can't take a backup like this).

Answer 2

If you are not using TDE with MSFT Key Management then Dragos Miron's answer is probably the easiest to implement and automate.

However, if you are using TDE or want to avoid the pain of taking another backup first (if you have a large database, for example), you can use the built-in (MSFT managed) backups of your database to do this. However, automating it might be a bit painful as you will need to create a service principal with sufficient permissions to do it unattended (outside the scope).

This link https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/recovery-using-backups?view=azuresql&tabs=azure-portal talks you through restoring a backup of a database using the Restore-AzSqlInstanceDatabase cmdlet. Just specify the source and target (and you can also specify a specific point in time) and it will restore it.

I don't think you can make this run on the SQL Agent of the Managed Instance itself, you will probably need to schedule it to run somewhere else, either another job server or an Azure runbook.