Bacpac Import fails with error 'The Element or Annotation class SqlDatabaseOptions does not contain the Property class CatalogCollation'

Question

I want to restore some production azure database on my local machine for development purposes. So I exported the production database to a .bacpack file & downloaded that file on local. Now I am trying to restore that file on my local machine using sqlpackage utility but it is throwing following error

*** Error importing database:An error occurred during deployment plan generation. Deployment cannot continue. The Element or Annotation class SqlDatabaseOptions does not contain the Property class CatalogCollation.

Can someone please tell me what does this error means & how to fix it?

Thanks in advance.

Answer 1

This has been fixed as part of build 14.0.17230.0 (not 14.0.17320.0) of SSMS. Please update to the latest version.

You can read more about here: https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/02/15/what-will-happen-with-catalog_collation-and-case-sensitive-vs-case-insensitive/

Answer 2

Install a later version of the Data Tier Application framework eg

https://www.microsoft.com/en-us/download/details.aspx?id=55088

That way you don't necessarily have to do a full upgrade, just use this standalone version..

An example import command would be:

"C:\Program Files\Microsoft SQL Server\140\DAC\bin\sqlpackage.exe" /a:Import /tdn:NEWDBNAME /tp:SQLSERVERUSERPASSWORD /tsn:SQLSERVERINSTANCE /tu:SQLSERVERUSERPASSWORD /sf:"C:\Temp\BACPACFILENAME.bacpac"

Answer 3

If you don't want to upgrade/install anything.

Just copy .bacpac file to your Azure storage and try Import Database option available in Azure Portal: