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

Babawale Dawodu 90 Reputation points
2024-07-09T09:06:52.5033333+00:00

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

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,333 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 19,781 Reputation points
    2024-07-09T09:59:00.43+00:00

    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.
  2. Erland Sommarskog 106.6K Reputation points
    2024-07-09T21:39:51.39+00:00

    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.