Restore SQL Server DB backup from Azure Blob into SQL Server Managed Instance using T-SQL

Restore Bak File from Blob to Azure Manged Instance.

This use case covers how a Microsoft SQL Server backup (.bak) file can be restored from Azure Blob Storage into an Azure SQL Server Managed Instance using T-SQL.
You can very well manually restore the backup file using Microsoft SQL Server Management Studio after connecting to the Azure QL Server Instance. On SSMS right click on the ‘Databases’ -> ‘Import Data-Tier Application’

Step 1: Connect to the Azure SQL Server Managed Instance using SSMS.
Step 2: Create a Master Key
The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.
Syntax:
CREATE MASTER KEY [ ENCRYPTION BY PASSWORD =’ password’ ]

E.g.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<A-7ea11y_5TRONG_9@$$MORD>’;

Step 3: Create a SAS Token for the Blob Storage Container.

Go to your Blob Storage Container and Settings -> Shared Access Tokens
Set the token expiry and required permissions (‘read’ should be selected to get read access to the container).

Generating SAS Token for Blob Container.

Step 4: Create a CREDENTIAL to connect to Azure Blob.

MS SQL Server requires credential to connect to resources outside of SQL Server. A CREDENTIAL can be scoped to a Server Level or to a database level. Use CREATE DATABASE SCOPED CREDENTIAL to limit the scope to the database.

Create the Credential by using the Secret from the Bob SAS.

Syntax:

CREATE CREDENTIAL credential_name

WITH IDENTITY = ‘identity_name’

[ , SECRET = ‘secret’ ]

[ FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name ]

CREATE CREDENTIAL credential_name
WITH IDENTITY = 'identity_name'
[ , SECRET = 'secret' ]
[ FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name ]

E.g.

CREATE CREDENTIAL [https://myazureblobstorage.blob.core.windows.net/bakfiledump]

WITH IDENTITY = 'SHARED ACCESS SIGNATURE'

, SECRET = 'sp=r&st=2022-01-03T20:21:33Z&se=2024-04-03T03:21:33Z&spr=https&sv=2020-08-04&sr=c&sig= SRPSWrWvtJ85pJ8r8zkWa77Z7Kav3YAgNGw33eKW34E8%3D'

Once the Credential is created, you can query the credential using

SELECT * FROM sys.credentials

Step: 5 Restore the BAK file from Blob.

RESTORE DATABASE ny_yellow_cab

FROM URL = https://myazureblobstorage.blob.core.windows.net/bakfiledump 083121/ny_yellow_cab.bak’

Voila, Once you refresh the server you can see the restored database.

If you have more than one database to restore you can create a PROCEDURE for the RESTORE command alone and pass the database name and path as a parameter. You can invoke the PROCEDURE remotely using Azure Data Factory.

References & Reads:

1. https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-sample-database-quickstart
2. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-and-restore-with-microsoft-azure-blob-storage-service?view=sql-server-ver15