Azure Data Factory set up automated, continuous, or on-demand restore of Azure SQL Managed Instance databases between two separate Azure subscriptions.
Before you start the database restore process, make sure to turn off TDE. Those who require Cross Subscription Database Restore for SQL Managed Instance Database with TDE enabled using ADF
Prerequisite
- Azure SQL Managed Instances are located across two distinct subscriptions.
- Azure Blob storage same subscriptions SQL Managed Instances are located
- Azure Data Factory 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'
GOValidate 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
