Share via

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

Babawale Dawodu 85 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,226 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 18,741 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.