Why would a .bacpac file take several hours to import?

Question

I'm developing an Umbraco 8 site and in the very early stages so an extremely small database set up on SQL Server 2017 Express.

I've got to the point where I'd like to transfer this to a server so that I can show other people. I've exported a .bacpac file using the Export Data Tier Application option in SQL Server Management Studio 2018 and copied this over to the server. The file is 55kb so would export it to import within seconds - if I do this locally, it does indeed complete quickly.

However, on the server when I use the Import Data Tier Application option, the database restore takes several hours, getting stuck on:

- Importing package schema and data into database
- Updating Database
- Importing Data
- Processing Import

It also seemed to stick on Processing Table '[dbo].[umbracoUser]' although I didn't monitor the whole process so not sure if that was just one of several slow imports.

The server also runs SQL Server 2017 Express and I'm importing through SQL Server Management Studio 2017. The server is running Windows Server 2016.

I've noticed this with a couple of databases and struggling to come up with any reasoning.

I've attempted to use SqlPackage.exe to import this too and that also appears slow.

Thanks,

Ben

Boorer

Check the activity on the SQL instance while it stuck to see if that provides some additional info, such as blocking. – Matte

Same for me, can anyone help me with this issue? – Pulpy

Are you transferring date from one on-premises SQL Server to another? Don't use bacpac, it's a horribly inefficient transfer mechanism. Use a backup file .bak – Bushey

Answer 1

Turns out that this was an issue with memory usage, when the import happens the memory ramped up to 100%. It does complete after a while but I ended up freeing up resources for the import.

Boorer

Answer 2

It can be solved by change the version of SQL Server Management Studio to a old version. I encountered this problem on 18.8 and after I change the version to 18.6 it worked.

Pulpy

Might be a coincidence, that. Should have answered this but for me it was a memory issue, during transfer the memory ramped up to 100% and when it does that it takes a long time to complete. I freed up some memory and it sorted my issue. – Boorer