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 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 questionmark ?

?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.

See Also