How to resolve ,Backup and Restore operations on Block Blobs are not supported when WITH CREDENTIAL syntax is used.

by

This Article guides about,

How to resolve ,Backup and Restore operations on Block Blobs are not supported when WITH CREDENTIAL syntax is used.

What is URL backups in SQL Server?

From SQL Server 2012 , we can take SQL database backups on Azure Blob Storage. This backup called URL backup. They are similar like disk backup but only difference is we are storing it on Azure location. This backup can be used to restore the database. It support all three type of SQL Server Backups(FULL, DIFFRENTIAL, T-LOG)

These backups are storing in azure storage as either Page blob or Block Blob. Both are available to take backup in SQL Server but Page blob having cost higher side than Block Blob.

Difference in this Block blobs are optimized for streaming and storing large amounts of data, append blobs allow for adding data to the end of an existing blob, and page blobs are optimized for random read and write operations.

If you want learn How to take SQL Database backup on Azure Storage?

Why below error occurred when backup file format is block blob?

IF you are taking backups in Page blob format , it support both backup method i.e. backup with credential option and without credential option as well.

But when backup file format is Block blob it only support for shared access signature and Credential need to be created with shared access signature only else we will get below error while taking backups.

You can not allowed to use with Credential option in Block blob URL backups

Msg 3271, Level 16, State 1, Line 3
A nonrecoverable I/O error occurred on file "https://omkarblob.blob.core.windows.net/backup/AdventureWorks2016_EXT.bak:"
The specified URL points to a Block Blob. Backup and Restore operations on Block Blobs are not supported when WITH CREDENTIAL syntax is used..
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

So to take backup in Block blob format your backup script will look like below and you should have credential created with identity as Shared Access Signature

USE master
CREATE CREDENTIAL [https://omkarblob.blob.core.windows.net/backup/] -- this name must match the container path, start with https and must not contain a trailing forward slash.
WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.
, SECRET = '' -- this is the shared access signature token get it from blob storage on Azure Portal

backup database [AdventureWorks2016_EXT] to
URL = 'https://omkarblob.blob.core.windows.net/backup/AdventureWorks2016_EXT.bak'
with stats =10

This Way you will able to take backup in Block blob format. This format is very useful when you have very large databases and you want to keep backup files for long duration. This helps to save the cost.

Leave a Comment