How to Import Large Bacpac into Azure SQL Database

TL;DR

Use SQLPackage tool (.Net Core version) running on Azure VM.

Disable QuickEdit Mode when running the command prompt.

Let Azure VM have a lot of RAM (64GB - D16ds_v4) and create a separate partition for Temp BacPac files (e.g. T:) 4 times the size of BacPac (e.g. 4TB).

mkdir T:\Temp
SET TEMP=T:\Temp
SET TMP=T:\Temp
sqlpackage.exe /a:import /tcs:"Data Source=[ServerName].database.windows.net;Initial Catalog=[DatabaseName];User Id=[UserName];Password=[Password]" /sf:E:\BacPacDir\BacPacFile.bacpac /p:DatabaseEdition=Hyperscale /p:DatabaseServiceObjective=HS_Gen5_2

Drink a sea of tea and relax! This import is going to take a while.

Slightly longer read

Once upon, there was a huge database (1 terabyte monster).

This database was no longer needed, so it was exported to the BacPac file by Elf the Exporter into the storage account to rest in peace forever.

"But wait", said Gandalf the Cautious, let's test if we can restore this BacPac treasure before we delete the database!

This is where the headache started.

Attempt #1: Import BacPac using the Azure Portal (fail)

There is a lovely, cosy button to import the database directly from BacPac seating in the storage account.

When you do that with 1TB BacPac, you may encounter this helpful error after a few hours into the importing process.

The ImportExport operation with Request Id [] failed due to 'The operation was interrupted and cannot continue. Please drop the database, re-create it, and try again.

I did precisely that five times, using different database models, vCore, DTU, provisioned, serverless, bunny-eared, all firewalls off, you name it, I've tried it.

Same error. Sad panda.

Attempt #2: Download BacPac into Azure VM and use SQL Management Studio (SSMS) to restore the BacPac

I ran the SSMS tool and started the Import Data-tier Application thing.

This went for a little bit, giving me a shade of hope. But when it started to restore the enormous table, it barked.

Scaling up the VM to have 64 GB of RAM made no difference, as it turns out, that the SSMS is 32-bit software and cannot use more than 4GB.

Attempt #3: Use the 64-bit SqlPackage tool to restore the damn BacPac

I've downloaded SqlPackage for .Net Core, which is 64-bit.

I've used a version named sqlpackage-win7-x64-en-US-15.0.5084.2.zip, which I've christened 'The Golden Saviour.'

I've run the following command:

sqlpackage.exe /a:import /tcs:"Data Source=[ServerName].database.windows.net;Initial Catalog=[DatabaseName];User Id=[UserName];Password=[Password]" /sf:E:\BacPacDir\BacPacFile.bacpac /p:DatabaseEdition=Hyperscale /p:DatabaseServiceObjective=HS_Gen5_2

If you wonder, what to put into /p:DatabaseEdition=[XXX] /p:DatabaseServiceObjective=[YYY], the SSMS Import Wizard Summary can help.

Note, that there is an extra DatabaseMaximumSize parameter for some of the database editions:

/p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P11 /p:DatabaseMaximumSize=1024

After running SqlPackage, I've received a 'There is not enough space on the disk' error, regarding space in TEMP location on C drive.

I've attached a 1 TB T: drive (for 1TB BacPac) and preceded SqlPackage run with the following commands:

mkdir T:\Temp
SET TEMP=T:\Temp
SET TMP=T:\Temp

Doing the above was still not enough and Temp drive got filled up completely, so I used a 4 TB drive.

Then I got this error.

*** An unexpected failure occurred: Data plan execution failed with message One or more errors occurred.
(One or more errors occurred. (One or more errors occurred. (The underlying compression routine could not reserve sufficient memory.))).

I've increased RAM from 32GB to 64GB and disabled the QuickEdit Mode in the command prompt to prevent an unexpected pause in execution.

After 28 hours, the SqlPackage BacPac import succeeded. Hallelujah!