Upload and restore .bak file to Azure SQL Database from Local Machine

Question

I have a .bak file on my machine I am trying to restore into an azure database. I then created an Azure storage/blob account to hold this file, to restore via ssms but unfortunately, the file is large (about 35GB) and crashes my machine multiple times.

How can I get this file from my local machine to Azure SQL database? Suggestions, please

Thanks

Answer 1

Restoring a .bak file directly to an Azure SQL Database is not supported because Azure SQL Database is a managed database service that does not provide the same access to the underlying file system as SQL Server running on a VM or on-premises. Here is a workaround :

1. Upload the .bak file to Azure Blob Storage:
   • Use Azure Storage Explorer or the Azure portal to upload the .bak file to a Blob Storage container.
   • Ensure that the Blob Storage account and container are accessible from your machine.
2. Create an Azure SQL Virtual Machine:
   • If you don't have an Azure SQL VM, create one from the Azure portal. This VM will run SQL Server, which allows restoring .bak files.
   • Connect to the VM using Remote Desktop Protocol (RDP).
3. Copy the .bak file to the SQL Server VM:
   • Once connected to the VM, use Azure Storage Explorer or the Azure portal to download the .bak file from the Blob Storage to the VM.
4. Restore the .bak file on the SQL Server VM:
   • Open SQL Server Management Studio (SSMS) on the VM.
   • Use the Restore Database wizard or T-SQL commands to restore the .bak file to the SQL Server instance on the VM.
5. Migrate the restored database to Azure SQL Database:
   • After the database is restored on the SQL Server VM, use the Data Migration Assistant (DMA) or the Azure Database Migration Service (DMS) to migrate the database to Azure SQL Database.

Answer 2

Not sure why Amira suggested that you would take the route over the Azure VM. You can just as well run DMA directly against your local server. I think it is also possible to do the migration by BACPAC, but if the backup is 35 GB, the bacpac may be of similar size.

In any case, you cannot restore the backup to Azure SQL Database directly by any means.

Answer 3

Sort of rediculous that MS wouldn't make a simple process for this... given that you would think they would WANT people migrating to the cloud, and this would be the simplest method for end users.