0

I've created a bacpac backup of my database (both Azure and local with SSMS). Trying to import the bacpac to same or another machine (again both Azure and local) the import always fails with this error:

Error SQL72014: .Net SqlClient Data Provider: Meldung 3729, Ebene 16, Status 4, Zeile 1 Cannot ALTER 'SecTable' because it is being referenced by object 'sec_SecTable'. Error SQL72045: Fehler bei der Skriptausführung. Ausgeführtes Skript: ALTER TABLE [dbo].[SecTable] SET (SYSTEM_VERSIONING = OFF); ...

SQL-Server versions: 14.0.3223.3 and 15.0.1800.32.

Already tried different versions of SSMS (17,18) and via SqlPackage.exe (Visual Studio 19 and SSMS 17/18) to execute the import/export of the bacpac.

The smallest DB creation script that will reproduce this problem is:

CREATE TABLE [dbo].[SecTable](
    [Id] [uniqueidentifier] NOT NULL DEFAULT (newid()),
    [SystemHistoryStart] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SystemHistoryEnd] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
    [SecValue] [int] NOT NULL
 CONSTRAINT [PK_SecTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([SystemHistoryStart], [SystemHistoryEnd])
) ON [PRIMARY]
WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[SecTable_History]))
GO

-- Function
CREATE FUNCTION [dbo].[sf_SchemaBoundTableFunction]
(
    @secValue int
)
RETURNS TABLE
WITH SCHEMABINDING
RETURN
    SELECT 1 AS Result
GO

-- security policy
CREATE SECURITY POLICY [dbo].[sec_SecTable] 
ADD BLOCK PREDICATE [dbo].[sf_SchemaBoundTableFunction]([SecValue]) ON [dbo].[SecTable] BEFORE DELETE
WITH (STATE = ON, SCHEMABINDING = ON)
GO

Any suggestions or workaround how to solve this problem or how to configure the import/export of the bacpac without removing the schemabinding? Is it maybe a bug or am I doing something wrong?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.