Cannot use the backup file because it was originally formatted with sector size 512 and is now on a device with sector size 65536
That was the error SQL Server output when a client attempted to restore a SQL database from a database backup file stored on a storage account. The reason for this error message is that the backup file was created with the different sector size.
The client had an old version of SQL Server and had locally backed up the database. They then uploaded the backup to an Azure storage account. However on the target machine they had the latest version of SQL Server and were attempting to utilise the native restore from URL feature. It was at that point when SQL Server spat out the error notifying the user that backup failed as sector sizes didn’t match up.
The Restore Database has a BLOCKSIZE parameter which specifies the physical block size, in bytes. The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. The default is 65536 for tape devices and 512 otherwise. Typically, this option is unnecessary because BACKUP automatically selects a block size that is appropriate to the device. Explicitly stating a block size overrides the automatic selection of block size.
If you plan to restore your database from a backup on an Azure Storage account, the easiest way, would be to choose the option to backup database to storage account directly (provided you are on a version of SQL Server that supports that feature). When backing up to URL, SQL Server uses the 64K block size by default. This is because the sector size presented by Azure blob storage is 64K. So backups to URL have the same block size as the media sector size and the issue reported above does not occur.
However when a backup is created on a local disk with 512 sector size and 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 due to a sector size mismatch. In order to use that specific backup file, the solution was to explicitly specify the original block size for the backup (512 in this case) using the BLOCKSIZE parameter of the RESTORE statement.
RESTORE DATABASE myDatabase FROM URL = @storageURI
WITH CREDENTIAL = @credentialName,
BLOCKSIZE = 512