Restoring to Azure SQL Managed Instance – Part 2

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!

5 thoughts on “Restoring to Azure SQL Managed Instance – Part 2

  1. Pingback: Dew Drop – October 13, 2021 (#3536) – Morning Dew by Alvin Ashcraft

  2. Pingback: Dew Drop – October 13, 2021 (#3536) - 51posts

  3. Pingback: Dew Drop – October 13, 2021 (#3536) - Online Code Generator

  4. Pingback: ➧Dew Drop – October 13, 2021 (#3536) • Softbranchdevelopers

  5. Pingback: Performing a Restore to SQL Managed Instance – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s