CODEWITHSUNDEEP
sqlazureazure-sql-databaseblobazure-blob-storage
Chris Green
Chris Green

Reputation: 21

Cannot open backup device from BLOB Storage - Access is Denied

I have a SQL backup in a storage container in Azure and am trying to restore the DB to my Azure SQL instance from another Azure VM through SSMS.

The public access level on the storage container is set to 'Blob'. My client has whitelisted the public IP of the Azure VM where I'm running SSMS for the restore.

I'm also logging into SSMS using an account that has db owner permissions.

The BLOB SAS I was given has full list and read-write access.

If I try to retrieve the file list OR try to do my restore, I get "Cannot open backup device..... Operating system error 5(Access is denied.). -- full error at bottom.

Any thoughts?

Creates credential

USE master CREATE CREDENTIAL [https://imis20prodstorageaccount.blob.core.windows.net/backups] WITH IDENTITY='SHARED ACCESS SIGNATURE' , SECRET = 'removed for posting' GO

Test retrieving file list from URL -- GENERATES ERROR BELOW

RESTORE FILELISTONLY FROM URL = 'https://imis20prodstorageaccount.blob.core.windows.net/backups/GGC_iMIS-5-10-21.BAK'

Restore from URL -- GENERATES ERROR BELOW

RESTORE DATABASE GGC_iMIS_TEST FROM URL = 'https://imis20prodstorageaccount.blob.core.windows.net/backups/GGC_iMIS-5-10-21.BAK'

Msg 3201, Level 16, State 2, Line 11 Cannot open backup device 'https://imis20prodstorageaccount.blob.core.windows.net/backups/GGC_iMIS-5-10-21.BAK'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 11 RESTORE FILELIST is terminating abnormally.

Upvotes: 2

Views: 1917

Answers (1)

IpsitaDash-MT
IpsitaDash-MT

Reputation: 1450

To match the SQL version, you'll need to check the SQL server version and build a temporary table with the right structure. The schema of your temp table must be identical to that of the command output.

A user can verify the backup file with the command RESTORE FILELISTONLY. You'll see that the database's updated logical name is corrupted or damaged, and the name's last character is truncated.

Referred Documents : RESTORE Statements - FILELISTONLY (Transact-SQL)

Similar Issue: Fix SQL Server Backup Error

Upvotes: 0

Related Questions