SQL Server bacpac fails on local import in latest update (Online index operations can only be performed in Enterprise edition of SQL Server)

Question

I have an SQL Azure database and connect to it in SQL Server Management Studio. I do Export Data Tier Application and then Import Data Tier Application for the .bacpac file to get it into my localdb. Or I use Tasks - Deploy Database.

Either way, it worked up until recently and now I get an error

Online index operations can only be performed in Enterprise edition of SQL Server

I am using SQL Server Management Studio versions below (from Help - About). Any ideas?

SQL Server Management Studio                    15.0.18369.0
SQL Server Management Objects (SMO)             16.100.46041.41
Microsoft Analysis Services Client Tools        15.0.19342.0
Microsoft Data Access Components (MDAC)         10.0.19041.1
Microsoft MSXML                                 3.0 6.0 
Microsoft .NET Framework                        4.0.30319.42000
Operating System                                10.0.19041

Pori

I wanted to add a comment with the full error message (for search reasons) because it took me awhile to discover this post and realize SSMS 18.8 was the culprit. I could hardly find any search results with the full error message: Could not load type 'Microsoft.SqlServer.TransactSql.ScriptDom.DropExternalStreamStatement' from assembly 'Microsoft.SqlServer.TransactSql.ScriptDom, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (Microsoft.Data.Tools.Schema.Sql) – Malapropos

Answer 1

If instead of using localdb you can upgrade to or use SQL Server 2019 Developer Edition, then you won't have any issues. Developer Edition is free and has the same features as Enterprise Edition. You can download Developer Edition from here and then update it with the latest cumulative update from here, after that try to import the bacpac to the Developer Edition instance.

Vanya

You say "instead of localdb". Will this edition let me maintain and run local copies of DBs on my laptop? – Pori

Yes. For sure. You can even sync your local databases with Azure if you like, instead of having to export from Azure SQL Database, download the bacpac and then import it into the SQL Server instance of your computer. – Vanya

I can add here the link how to upgrade - learn.microsoft.com/en-us/sql/database-engine/install-windows/… . I updated from Express to Developer version via SQL Server Installation Center from Configuration Tools - Maintenance in few minutes. – Milline

Answer 2

Among bug fixes in SSMS 18.9:

Fixed in issue where importing a bacpac into SQL Express may fail. See SQL Server user feedback.

This issue with SSMS 18.8 had been acknowledge by the product team, but was fixed:

I apologize for the inconvenience of this issue, we are working to include a fix in the next release of SSMS. In the meantime, installing SSMS 18.7.1 (https://learn.microsoft.com/sql/ssms/release-notes-ssms?view=sql-server-ver15#1871) will allow you to complete the bacpac import operations on SQL Server Express.

Rager

Thank you! I spent way too much time spinning my wheels on this problem. This saved the day. – Pigweed

As indicated in the feedback link, this was fixed in was fixed in SSMS 18.9. So just download the latest version of SQL Management Studio – Goodloe

Answer 3

I just ran into this same exact problem again today. I exported our database from Azure into a BACPAC and was having trouble with SSMS loading it into SQL Express.

Just by pure luck, I found SSMS 18.9 was released today and appears to have fixed the issue in my case.

Spoon