question

Lalit-9297 avatar image
0 Votes"
Lalit-9297 asked ·

Unable to backup sql azure to a bacpac in blob

Hi, I have been trying repeatedly to backup a sql azure database to a bacpac file on blob. Each time I do, it fails with an error One or more unsupported elements were found in the schema used as part of a data package. Error SQL71501: Error validating element [dbo].[ScanInCacheFromDatabase]: Function: [dbo].[ScanInCacheFromDatabase] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [qp].[query_plan].[exist], [sys].[dm_exec_query_plan].[qp], [sys].[dm_exec_query_plan].[query_plan], [sys].[dm_exec_query_stats].[qp] or [sys].[dm_exec_sql_text].[qp]. (Microsoft.SqlServer.Dac) Any suggestions on how to make this work? Tx

azure-sql-database
· 3
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Lalit-9297, Did you create a DAC extract from this database or was this database created from a DAC or is it being used as part of Visual Studio project?

The specific error: Error SQL71501: Error validating element [dbo].[ScanInCacheFromDatabase]: Function: [dbo].[ScanInCacheFromDatabase] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects:

[qp].[query_plan].[exist],
[sys].[dm_exec_query_plan].[qp],
[sys].[dm_exec_query_plan].[query_plan],
[sys].[dm_exec_query_stats].[qp] or
[sys].[dm_exec_sql_text].[qp]


Do any of these exist in your database under Views -> System Views?

1 Vote 1 · ·
Lalit-9297 avatar image Lalit-9297 Mike-Ubezzi-MSFT ·

IHi,

I had a database in azure. I was trying to download it on my local machine. In order to do so I had gone to sql management studio, connected to the database, clicked Tasks | Export Data-Tier Application. This when it runs for a while and fails with the above error message.

0 Votes 0 · ·

Hi @Lalit-9297 - Is this still an issue? I have a feeling that if your query plan was refreshed manually or through Tuning Advisor, this issue may resolve itself. The other point is to ensure you are using the latest version of SSMS, and is there an existing file in the %\Documents\SQL Server Management Studio\DAC Packages% ?

Download SQL Server Management Studio (SSMS)

If this is the first time you have exported this database, or it has grown in size since the last export, you will need to consider clustered indexes for larger tables, etc.


0 Votes 0 · ·
Lalit-9297 avatar image
0 Votes"
Lalit-9297 answered ·

It has worked, tx everyone. I upgraded to P1 and deleted the procedure [ScanInCacheFromDatabase]. The procedure wasn't made by me, maybe a Azure system proc.

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LeonLaude avatar image
0 Votes"
LeonLaude answered ·

Hi,

Something here may help:
https://techcommunity.microsoft.com/t5/azure-database-support-blog/exporting-a-database-that-is-was-used-as-sql-data-sync-metadata/ba-p/369062

Also check the "considerations" section over here:

Export to a BACPAC file - Azure SQL Database and Azure SQL Managed Instance
https://docs.microsoft.com/en-us/azure/azure-sql/database/database-export

Best regards,
Leon

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.