So in the last blog we confirmed that we could move to SQL MI via some analysis, this is now time to actually do a backup and restore via URLs to move data.

Quite simply you need to BACKUP to URL (Azure Storage container) and the setup requirement is that you need to create a SQL credential that holds the SAS token – this is what allows authentication to the container to take place. Summarised as:

1. Create SQL credential on the source SQL server. The secret is the SAS token.

IF NOT EXISTS  
(SELECT * FROM sys.credentials   
WHERE name = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>')  
CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] 
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
   SECRET = '<SAS_TOKEN>';

How would you generate the SAS token? Personally, I use Azure Storage Explorer but you could use PowerShell too. See this link to understand this area https://blobeater.blog/2019/09/08/azure-what-is-a-shared-access-signature/

2. Issue BACKUP command to URL (which is to the storage container)

BACKUP DATABASE [AKSDWH] TO  URL
 = N'https://mainsql.blob.core.windows.net/backupsbak/aksdwh_backup_2021_08_09_142718.bak' WITH  COPY_ONLY, 
 NOFORMAT, NOINIT,  NAME = N'AKSDWH-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

3. Login to the Managed Instance and create the same credential to allow restore such as:

USE [master]
RESTORE DATABASE [AKSDWH] FROM  
URL = N'https://mainsql.blob.core.windows.net/backupsbak/aksdwh_backup_2021_08_09_142718.bak'

GO

Login to the Azure Portal and check:

Beats the BACPAC route!