Impossible to generate a BACAP file from Azure SQL database

Question

Impossible to generate a BACAP file from Azure SQL database

sergecal 26

Dear All,

We have an Azure SQL databse which is from a third party app provider (Atlassian Confluence)
We simply try to Export the database to a BACAP file in orer to be Re imported into an other location

We face to a wierd error when starting the export process either from Azure, SQL Server Studio, or SQL package

The error return is as below :

TITLE: Microsoft SQL Server Management Studio

One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71561: Error validating element [dbo].[denormalised_content_trigger]: Trigger: [dbo].[denormalised_content_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[CONTENT]. External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element [dbo].[denormalised_content_trigger]: Trigger: [dbo].[denormalised_content_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[CONTENT].[CONTENTID]. External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element [dbo].[denormalised_content_trigger]: Trigger: [dbo].[denormalised_content_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[CONTENT].[CONTENTTYPE]. External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element [dbo].[denormalised_content_trigger]: Trigger: [dbo].[denormalised_content_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[CONTENT].[PREVVER]. External references are not supported when creating a package from this platform.
Error SQL71561: Error validating element [dbo].[denormalised_content_permission_trigger]: Trigger: [dbo].[denormalised_content_permission_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[CONTENT_PERM]. External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element [dbo].[denormalised_content_permission_trigger]: Trigger: [dbo].[denormalised_content_permission_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[CONTENT_PERM].[CPS_ID]. External references are not supported when creating a package from this platform.
Error SQL71561: Error validating element [dbo].[denormalised_content_perm_set_trigger]: Trigger: [dbo].[denormalised_content_perm_set_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[CONTENT_PERM_SET]. External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element [dbo].[denormalised_content_perm_set_trigger]: Trigger: [dbo].[denormalised_content_perm_set_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[CONTENT_PERM_SET].[CONTENT_ID]. External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element [dbo].[denormalised_content_perm_set_trigger]: Trigger: [dbo].[denormalised_content_perm_set_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[CONTENT_PERM_SET].[ID]. External references are not supported when creating a package from this platform.
Error SQL71561: Error validating element [dbo].[denormalised_space_trigger]: Trigger: [dbo].[denormalised_space_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[SPACES]. External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element [dbo].[denormalised_space_trigger]: Trigger: [dbo].[denormalised_space_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[SPACES].[SPACEID]. External references are not supported when creating a package from this platform.
Error SQL71561: Error validating element [dbo].[denormalised_space_permission_trigger]: Trigger: [dbo].[denormalised_space_permission_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[SPACEPERMISSIONS]. External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element [dbo].[denormalised_space_permission_trigger]: Trigger: [dbo].[denormalised_space_permission_trigger] has an unresolved reference to object [db-az-prd-confluence].[dbo].[SPACEPERMISSIONS].[SPACEID]. External references are not supported when creating a package from this platform.
(Microsoft.SqlServer.Dac)

We cannot change anything into the database structure has is is the properperty of Atlassian and change might affect app behavour

Any idea how to approach this error return ?

For infomarion, the database size is about 100 GB

Thanks for help
regards

2 answers

Your answer

Answer 1

That Azure SQL Database related to the application Atlassian Confluence has tables with triggers that make reference to database objects that are part of another databasar named [db-az-prd-confluence].

Since the database is related to a third-party software vendor then you cannot touch it. The vendor has to fix/remove those external references from the triggers or remove the triggers. Once they do that then you will be able to export the database as a bacpac.

Another option you have is to create a copy of the Azure SQL database you want to export, then you can comment the external references of the triggers or drop the triggers. After that you will be able to export the copy of the database as a bacpac.

Answer 2

sergecal 26

Dear @Alberto Morillo , thanks for your feedback

I have open all table trees from SQL server studio and check the Triggers object
I have found 3 tables only with a triger which are defined as below sample

CREATE TRIGGER [dbo].[denormalised_space_trigger]
ON [db-az-prd-confluence_copy_22-03-09].[dbo].[SPACES]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
if ([dbo].space_function_for_denormalised_permissions() = 1)
BEGIN
INSERT INTO DENORMALISED_SPACE_CHANGE_LOG (space_id) select SPACEID from inserted;
INSERT INTO DENORMALISED_SPACE_CHANGE_LOG (space_id) select SPACEID from deleted;
END
END

GO

ALTER TABLE [dbo].[SPACES] ENABLE TRIGGER [denormalised_space_trigger]
GO

I did not see a reference to an external object as the error mentionned, any idea ?

regarsd

Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2022-03-09T17:04:30.683+00:00
Please use this query to identify what table is using the trigger named "denormalised_content_permission_trigger":

SELECT table_name = so.name ,trigger_name = st.name ,trigger_text = sc.text ,create_date = st.create_date FROM sys.triggers st JOIN sysobjects so ON st.parent_id = so.id JOIN syscomments sc ON sc.id = st.[object_id]

AFter that examine the table using SQL Server Managment Studio:

When you make a right click on the trigger, and click Modify, search (CTRL + F) for the keyword "db-az-prd-confluence".

Hope this helps
sergecal 26 Reputation points

2022-03-10T09:03:01.863+00:00

Hello,

I have run your querry on the database table and it identified only one table using the denormalised_content_permission_trigger

As you can see from querry result, the trigger_text column show the trigger text which refer to the source database from which the current db has been copied
But when I go to that table and right click on the trigger and Modify, it did not show the db reference but only the trigger script as shown below :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[denormalised_content_permission_trigger]
ON [db-az-prd-confluence_copy_22-03-09].[dbo].[CONTENT_PERM]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
if ([dbo].content_permission_function_for_denormalised_permissions() = 1)
BEGIN
INSERT INTO DENORMALISED_CONTENT_CHANGE_LOG (cps_id) select CPS_ID from inserted;
INSERT INTO DENORMALISED_CONTENT_CHANGE_LOG (cps_id) select CPS_ID from deleted;
END
END

IMPORTANT :
I have try to run the Export on the source database , as mentionned in trigger text , BUT the export fails with same error

What could be done to make that export working ? desabling/deleting triggers ?

regards
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2022-03-10T10:44:02.323+00:00

Create a copy of the database, drop the trigger on the copy of the database, and export the copy of the database.

Share via

Impossible to generate a BACAP file from Azure SQL database

TITLE: Microsoft SQL Server Management Studio

2 answers

Your answer