Azure Data Factory (ADF) set up automated, continuous, or on-demand restoration of Azure SQL Managed Instance (MI) databases between two separate subscriptions.

Before you start the database restore process, make sure to turn off TDE. For those who need TDE enabled, check out the ABC blog for guidance.

Prerequisite

Azure SQL Managed Instances are located across two distinct subscriptions.
Azure Blob storage same subscriptions SQL Managed Instances are located
Azure Data Factory (ADF) instance

Permission requires

To perform backup and restore operations, the SQL Managed Instance Managed Identity needs to have the “Contributor, Storage Blob Data Contributor” permission for the blob storage.
To transfer backup files between two storage locations, ADF managed identity needs the “Storage Blob Data Contributor” permission for the blob storage.
To carry out backup and restore operations, ADF managed identity needs ‘sysadmin’ permissions on SQL Managed Instance.

Note: We utilized Managed Identity for permission granting. Should you employ a different ID, ensure it has the same permissions assigned.

Step: 1

Creates a server-level credential. A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server.

USE master
GO
CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>]
WITH IDENTITY=’Managed Identity’
GO

Validate the credential created successfully

Step: 2

Create ADF link service connects for both SQL Managed Instances and storage accounts.
Create ADF dataset using both SQL Managed Instances and storage accounts link services

Step: 3

If you’re utilizing a private endpoint, make sure to set up an ADF integration runtime and a managed link follow Create Azure Data Factory Managed Private Links

Step: 4

Create ADF pipeline to take database backup from source.

Split backup files into multiple files for faster backup
Use below scripts to take copy only database backup
Use Script activity to execute the backup scripts using source SQL MI link service

Uas Master
GO
BACKUP DATABASE [@{pipeline().parameters.source_database_name}]
TO URL = N’https://<storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_01.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_02.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_03.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_04.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_05.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_06.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_07.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_08.bak’
WITH COPY_ONLY, MAXTRANSFERSIZE = 4194304, COMPRESSION, STATS = 10

Allow a minute for the backup to transfer to blob storage, adjusting the duration to meet your specific needs.

Step: 5

Create ADF pipeline to copy database backup files from source storage account to target storage account.

Use copy activity to copy backup files from source storage account to target storage account.
Allow a minute for the backup to transfer to blob storage, adjusting the duration to meet your specific needs.

Step: 6

Create Azure Data Factory pipeline to restore database to a target SQL Managed Instance backup from the designated storage account.

Use below scripts to restore database from designated storage account
Use Script activity to execute the restore scripts using target SQL MI link service

USE [master]

RESTORE DATABASE [@{pipeline().parameters.target_database_name}] FROM
URL = N’https://<storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_01.bak’,
URL = N’https://<storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_02.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_03.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_04.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_05.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_06.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_07.bak’,
URL = N’https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_08.bak’

Step: 7

Set up an additional pipeline to remove orphan databases users, provide user access, or carry out any extra tasks needed after a restore, using the suitable activity task.

Step: 8

Create ADF pipeline workstation to execute all Step4 > Step5>Step6>Step7 in sequence.

Set up parameters for both the source_database_name and target_database_name to enable dynamic operation of the pipeline across different databases.

​Microsoft Tech Community – Latest Blogs –Read More