Is is possible to use the Az.sql module to Restore a SQL Server .bak File to an Azure Managed Instance Database?

Question

I am tasked with restoring a .bak file in order to copy the data to Azure data Lake. I do not have the option to restore the .bak file to local database and I am hoping it may be possible to restore the .bak file using the AzSql module or via an ADF pipeline using the RESTORE DATABASE from URL command where the .bak file is in Azure Storage account which may be accessed by the SQL managed instance.

Answer 1

I don't know how to do it using CLI, but below you will find how to do it using T-SQL on SSMS while been connected to an Azure SQL Managed Instance.

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

You can also use PowerShell. You may need to install SQL Server PS Module.

#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'"