Restoring Database from Azure Blob Storage failing due to different sector size error

2
votes

I am trying to import SQL Server Database from AWS SQL Server to Azure SQL Server. Below are the steps I am doing for the import process :

  1. Backing up the database from AWS SQL Server to AWS S3 bucket.
  2. Copying the .bacpac file from S3 bucket to local file path.
  3. Uploading the .bacpac file from local file path to Azure blob storage.

After the upload process, when I am restoring the .bacpac file from Azure blob storage to Azure SQL Server, I am getting the below exception.

*Cannot use the backup file 'https://sqlmigrateassistant.blob.core.windows.net/sql2005backup/demodatabase.bacpac' because it was originally formatted with sector size 512 and is now on a device with sector size 65536. RESTORE FILELIST is terminating abnormally *

After step 2 above , I had also tried importing the .bacpac file from the local file path manually into Azure SQL Server from the SQL Server Management studio(SSMS).At that time I am getting the below exception.

*File contains corrupted data. (Microsoft.Data.Tools.Schema.Sql) *

Can anyone provide any information on how I can restore the database to Azure SQL Server ?

Any information is greatly appreciated.

1
sql-serverazureamazon-web-servicesamazon-s3

1 Answers

2
votes

You will have to use Restore database with URL and specify the block size of 512..

RESTORE DATABASE [AdventureWorks2012] FROM  URL = N'your bacpac path'
WITH  CREDENTIAL = N'My_Credential'
, FILE = 1,  NOUNLOAD,  STATS = 5,BLOCKSIZE= 512;

the reason for failure has been explained here:Database restore failure when restoring from URL and the context applies to your scenario as well

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