Restore from Azure Storage DB to sqlserver 2014

Question

I am trying to restore the Database from Azure Storage to Sql server 2014 but getting the error "TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

---

ADDITIONAL INFORMATION:

A nonrecoverable I/O error occurred on file "https://*****.blob.core.windows.net/sql_backup/Test_DB.bak:" The specified URL points to a Block Blob. Backup and Restore operations on Block Blobs are not permitted.. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3271).

I am restoring the DV using SSMS from URL option

SSMS_Restore_From_

Any suggestions please ?

Answer 1

SQL SERVER – Unable to Restore from URL – The Specified URL Points to a Block Blob. Backup and Restore Operations on Block Blobs are not Permitted

Caution

If you choose to copy and upload a backup file to the Windows Azure Blob storage service, use page blob as your storage option. Restores from Block Blobs are not supported. RESTORE from a block blob type fails with an error.

SOLUTION / WORKAROUND

It looks like, the backup file was turned into a block blob, and that explains the cause of the error during restore. I think the above is implying that the transfer tool (AZCopy) may have been configured to use block blob to transfer the backup file by default. After reading further about the tool, I found there is a parameter called /BlobType:Page. Once we used that parameter, restore worked like a charm.

• Saira: Thank you for the reply. I have tried using Page Blob as well but that also give errors Cannot use the backup file 'https://****.core.windows.net/***/Test_2_.bak' because it was originally formatted with sector size 4096 and is now on a device with sector size 65536. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3268)