I was testing the backup with multiple backup files (6) and I was able to successfully take backup for one of my large database (3TB) to my azure storage account by using MAXTRANSFERSIZE, BLOCKSIZE parameters.
Then I tried to restore the database using the multiple files. Since it's a very large database and it takes few hours to restore I tried to run RESTORE FILELISTONLY from URL to verify if I was able to get the list of files. When I ran the RESTORE FILELISTONLY statement I have received the below error
Cannot open backup device ‘https://mystorageaccount.blob.core.windows.net/sqlbackup/ServerName-Test_DB_FULL_20200912_030001_1.bak’. Operating system error 2(The system cannot find the file specified.)
I used SAS key to create the SQL Credential and hence my blobs are block blobs. I have also created access policy on my container with all types of permission exists on the policy (racwdl). The SAS key was generated on the azure portal with 1 year validity. Do I need to provide the read, write permissions while creating the SAS key (which can be achieved only through Azure Storage Explorer).
If I replace the SAS key with the newly generated key with read write permission, would I be able to restore with the backups which were taken with the old SAS key?
Or Am I missing anything? Please advice
Best Answer
I found the reason why I was not able to get the list from the backup files. Since I used Ola Hallengren's script to run my backup the files were saved in a sub folder with Instance_name>Database_name>FULL>Backupfile_name. I supposed to provide the actual backup path of the backup file by going into the properties of the backup file and then copy the path from there. Instead I was just copying the container path and then adding the backup file at the end.
It looked something like below.
https://storagesqlbackups.blob.core.windows.net/fullsqlbackup/dev-sql-01/Test_DB/FULL/dev-sql-01_Test_DB_FULL_20200821_220001.bak