Import bacpac fails with “does not contain QueryStoreStaleQueryThreshold”

13
votes

I am getting the error below when trying to import bacpac file from Azure sql database with management studio 2014. I have upgraded the Azure sql database to v12 and suspect it has something to do with this, but it works with some v12 databases and fails on some.

Does anyone know how to resolve this?

Could not load schema model from package. (Microsoft.SqlServer.Dac)

------------------------------ ADDITIONAL INFORMATION:

The Element or Annotation class SqlDatabaseOptions does not contain the Property class QueryStoreStaleQueryThreshold. (Microsoft.Data.Tools.Schema.Sql)

4
azureazure-sql-databasebacpac
Have you updated SSMS with the CU6 bits? – elfisher
Follow this thread I have the same issue, is a bug, I executed the query mentioned on the Azure DB and exported the BACPAC after that, and is working now. it started on October 22th 2015 social.msdn.microsoft.com/Forums/azure/en-US/… – Hector Sanchez

4 Answers

10
votes

Let me try and help here. We've seen other users report this when they have also enabled the new Index Advisor feature. There is a workaround along with a detailed description of the issue here. https://social.msdn.microsoft.com/Forums/azure/en-US/ac9deff4-e0fe-4de0-98ec-9059e917e0bc/database-export-bacpacs-can-no-longer-be-imported-sqldatabaseoptions-querystoredesiredstate?forum=ssdsgetstarted

5
votes

This did the trick for me..

ALTER DATABASE [Database-Name] SET QUERY_STORE = on;
GO
ALTER DATABASE [Database-Name]
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = 
    (STALE_QUERY_THRESHOLD_DAYS = 367)
);
GO
ALTER DATABASE [Database-Name] SET QUERY_STORE = off;
GO

I made a new bacpac and imported that one and it worked..

0
votes

I experienced the same problem with Management Studio 2016 CTP 2.3 (August preview). I have updated to Management Studio 2016 CTP 3.0 (October preview) and the .bacpac import works as a charm.

0
votes

I had same issue, I got Index Adviser active. I had SQL server 2014 Management studio. I upgrade to SP1, and try to install some hotfix, but nothing work. I was about to install 2016 edition, but then as last try instead of downloading Bacpac from Azure Portal, I use Management studio 2014 sp1 to connect to my DB and Export the Bacpac, and then try to import that Version it goes through fine.

I don't have to run those queries to turn off adviser, or any alteration. So just want to suggest if you have SQL server 2014 Edition upgrade it to SP1 and then export DB using it, this ensure that you create Bacpac that is compatible with that Management studio version as management studio is one that is writing the bacpac. Hope that help someone, who don't want to alter db.

on my machine, download bacpac from Azure's portal still don't work.