Azure Custom Script to restore database from Blob Storage to Managed Instance

0
votes

I need a post VM deployment script in PS to restore a .BAK file located in a Blob Storage to an Azure Managed Instance. I can manually do it manually using SMSS but I get the following error when trying to restore the file from a SQL query (image of error in link below because I do not have enough rep to post images)

Query I'm actually running:

CREATE CREDENTIAL blobstorage
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '$StrgAccSAS';

RESTORE DATABASE [$DBName] 
FROM URL = '$URL'
WITH CREDENTIAL 'blobstorage';

The requested parameters are correctly passed when executing the script.

Restore error

1
sqlazuressmsazure-sql-managed-instance

1 Answers

1
votes

When creating a credential with the SHARED ACCESS SIGNATURE you must:

  • name the credential the same as the container path
  • it must start with https
  • it cannot contain a trailing forward slash

Once you've done that, you do NOT (and can not in Managed Instances) use the WITH CREDENTIAL option. SQL will use the credential you set up that matches the URL you're restoring from:

RESTORE DATABASE [$DBName] 
FROM URL = '$URL'