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:
- 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!
Pingback: Dew Drop – October 13, 2021 (#3536) – Morning Dew by Alvin Ashcraft
Pingback: Dew Drop – October 13, 2021 (#3536) - 51posts
Pingback: Dew Drop – October 13, 2021 (#3536) - Online Code Generator
Pingback: ➧Dew Drop – October 13, 2021 (#3536) • Softbranchdevelopers
Pingback: Performing a Restore to SQL Managed Instance – Curated SQL