SQL Server 2014 and later includes a feature named Managed Backup, which automates scheduled database backup tasks. Managed Backup backs up databases to an Azure storage account using Backup to URL. As a part of its functionality, Managed Backup attempts to automatically resolve operational errors such as broken backup chains. Specifically, if an out of band backup is created by something other than Managed Backup itself, it will try to copy that backup to the storage account, in order to avoid breaking the backup chain.
Recently I have encountered a scenario where this Managed Backup behavior caused an apparent failure to restore a database from backup. A simple workaround exists, and this blog is to describe the issue and the workaround.
I was adding a database to an AlwaysOn availability group using the Add Database wizard. As a part of the wizard, a full backup of the database is taken, and stored on a local disk or share. When a backup is created on a local disk, its block size by default matches the sector size of the disk media. In my case, that was 4096 bytes. Once that backup was created by the wizard, Managed Backup detected that it was an out of band backup, and copied it to the storage account.
Later, I had to restore the database from this backup. However, I received an error:
Msg 3268, Level 16, State 1, Line 46
Cannot use the backup file ‘https://<>.blob.core.windows.net/5777ec70fa184f75a1cb84c660bd97b8/Test1_139d0c9ccecb453d8ab6a4e3fcf6440c_6a40325f71614c949532603886de9270_20151021122734.bak’ because it was originally formatted with sector size 4096 and is now on a device with sector size 65536.
Msg 3013, Level 16, State 1, Line 46
RESTORE HEADERONLY is terminating abnormally.
When backing up to URL, SQL Server uses the 64K block size by default because the sector size presented by Azure blob storage is 64K. Therefore, regular scheduled backups to URL created by Managed Backup have the same block size as the media sector size, and this issue does not occur. But when a backup created on a local disk with 4K sector size is copied to the storage account, and then an attempt is made to restore from it, SQL Server blocks it with the above error message.
The workaround in this case is to explicitly specify the original block size for the backup (4K in this case) using the BLOCKSIZE parameter of the RESTORE statement:
RESTORE
HEADERONLY
FROM URL =
‘https://<>.blob.core.windows.net/5777ec70fa184f75a1cb84c660bd97b8/Test1_139d0c9ccecb453d8ab6a4e3fcf6440c_6a40325f71614c949532603886de9270_20151021122734.bak’
WITH
CREDENTIAL
=
‘CredentialName’,
BLOCKSIZE
= 4096;
Incidentally, the same problem also causes the restore UI in SSMS to fail when enumerating backupsets within such a backup. It uses the SMO method that executes RESTORE HEADERONLY on the backup blob, which does not include the BLOCKSIZE parameter, and fails with the same error.
I would like to thank my colleague Nacho Alonso Portillo (blog) for his help in debugging this problem.
© 2019 Microsoft. All rights reserved.