We encountered an error while taking a backup of a database from Azure SQL Managed Instance to Blob. This issue is not specific to Azure Managed Instance (MI), but error indicates a problem with accessing the blob storage. Upon investigation, we determined that the Share Access Token (SAS) token had expired, which was causing the connection to the blob storage container to fail.
We discovered that while attempting to restore the backup using SSMS, the available backup file was not visible. Additionally, while utilizing a T-SQL command to restore the backup, an access error was raised.
Backup failed for Server bla bla bla bla
(Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: Cannot open backup device
.... Operating system error 50 (The request is not support.). (Microsoft.SqlServer.Smo)
SQLFirst check the current credential by performing a new query
select * from sys.credentials
SQLIf any old expired Share Access Token (SAS) is present, it should be drop.
DROP CREDENTIAL [yourcredentialName];
SQLVerify that the credential has been drop by performing a new query
select * from sys.credentials
SQLNow need to create a new SAS token with valid permission such as list, read, write permission and also created the new credential to access the blob storage. (first we deleted the old one).
IF NOT EXISTS
(SELECT * FROM sys.credentials
WHERE name = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>')
CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS_TOKEN>';
SQLAfter creating fresh credential we tried to restore database from blob using T_SQL and it was successfully restored. We validated the data and schema both were present there.
USE [master]
RESTORE DATABASE [SQLTestDB] FROM
URL = N'https://xxxxx.blob.core.windows.net/yyyyyy/zzzz_db_backup.bak'
SQL