Azure SQL: How To Restore Database In SQL Managed Instance

Article
1/17/2024

Introduction

In this post, we will see how we can easily restore a database in Azure SQL Managed Instance. There are two ways to do this, using T-SQL and Powershell.

Using Powershell

Before executing the following Powershell Script to restore the database we must be sure for the following prerequisites:

Powershell command line Shell
SqlServer Powershell module
SQL Managed Instance
Azure Storage Account

Install Or Update SqlServer PS module

To install the SqlServer module execute the following command:

#Install The SqlServer module if not installed
Install-Module -Name SqlServer
 
#Alternatively Use The "-AllowClobber" parameter if previous version of the SqlServer module is already installed
Install-Module -Name SqlServer -AllowClobber 
 
#Update The SqlServer module if already installed
Update-Module -Name SqlServer

#Type the Managed instance admin login
$username =  "#########"
 
#Type the Managed instance admin password
$password =  '################'
 
#Type the Full Managed instance name
$managedInstance =  "############.9ab5d2b08bb9.database.windows.net"
 
#Leave this parameter as is
$database =  "master"
 
#Before execute the Invoke-Sqlcmd, type the address with the full database backup path
 
Invoke-Sqlcmd -ServerInstance $managedInstance -Database `
$database -Username `
$username -Password $password `
-Query "RESTORE DATABASE [mydb] FROM URL = 'https://#########.blob.core.windows.net/files/databases/mydb.bak'"

Using T-SQL

Before executing the following T-SQL Script to restore the database we must be sure for the following prerequisites:

SSMS (SQL Server Management Studio) - Download the latest version from this link.
SQL Managed Instance
Azure Storage Account

On the T-SQL restore procedure, we must create a Credential in the Instance.

From the Storage Account main blade on the left, select Settings - Shared access signature
**
**

Change the Start, End date and time values and click the button Generate SAS and connection string

Caution
From the SAS token REMOVE the question mark ?

?sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-01-25T20:52:31Z&st=2019-01-10T12:52:31Z&spr=https&##################################################

By using the fixed SAS token, we create the Credential by giving as name the storage container address, where the database backup is located. And execute the RESTORE DATABASE command, to start database restoring.

CREATE CREDENTIAL [https://#########.blob.core.windows.net/databases]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
, SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-01-25T20:52:31Z&st=2019-01-10T12:52:31Z&spr=https&##################################################'
  
  
RESTORE DATABASE [mydb] FROM URL =
  'https://#########.blob.core.windows.net/databases/mydb.bak'

Summary

We just read how and with what tools we can restore a database backup to an Azure SQL Managed Instance. The restore process does not differ much from what we use in our everyday lives.

Share via