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 :
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.
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