11

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vince
  • 583
  • 5
  • 20
  • 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) – Gage Trader Feb 18 '21 at 21:08

3 Answers3

18

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.

dahlbyk
  • 75,175
  • 8
  • 100
  • 122
  • Thank you! I spent way too much time spinning my wheels on this problem. This saved the day. – Steve Mar 17 '21 at 19:01
  • 1
    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 – Mick Aug 11 '21 at 01:09
2

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.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • You say "instead of localdb". Will this edition let me maintain and run local copies of DBs on my laptop? – Vince Jan 04 '21 at 21:58
  • 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. – Alberto Morillo Jan 04 '21 at 22:21
  • 1
    I can add here the link how to upgrade - https://learn.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-to-a-different-edition-of-sql-server-setup?view=sql-server-ver15 . I updated from Express to Developer version via SQL Server Installation Center from Configuration Tools - Maintenance in few minutes. – Anton Semenov Nov 24 '21 at 13:31
1

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Kyle Weishaupt
  • 79
  • 9