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_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:

  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

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Elixir/Phoenix with Gitlab Auto DevOps to an AWS EC2 instance

Soft Skills For Solution Architects — Moving Beyond Technical Competence

21 VSCode Keyboard Shortcuts You Should Know

Python Basics: Arithmetic Operations

Gearing up in Python Part 1

How to “Google It” like a Senior Software Engineer

Bare Asterisk in Python

Having fun with Typeform Embed

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Abi Sr

Abi Sr

More from Medium

Building a Slack Slash Command with Azure Logic Apps

Main overview screen of Slack App, highlighting where to add slash commands

VanHack

Python Class Vs Method Class

How to access custom attributes from Azure AD-B2C using Graph API(s)