Jan 24, 2022
Restore SQL Server DB backup from Azure Blob into SQL Server Managed Instance using T-SQL
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).
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_nameWITH 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:
