SQL Azure Database Unable to restore bacpac due to foreign key conflict

Question

I'm attempting to restore a backup (.bacpac) of one SQL Azure database to another SQL Azure database but I am getting the following error:

Error encountered during the service operation. Could not import package. Error SQL72014: .Net SqlClient Data Provider: Msg 547, Level 16, State 0, Line 3 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_x_xx". The conflict occurred in database "x", table "dbo.x". Error SQL72045: Script execution error. The executed script: PRINT N'Checking constraint: FK_x_xx[dbo].[x]'; ALTER TABLE [dbo].[x] WITH CHECK CHECK CONSTRAINT [FK_x_xx];

I have also attempted to restore locally without success with a mscorlib exception. And I've also checked the live database and I can't see why this key is being violated.

Answer 1

Since tha bacpac file is not transactional, so when the bacpac is being generated, writing new rows to your target database will end up corrupting the index. The database should either have no other users, or it should be copied and a bacpac should be made from the copy.

1. Copy the target database (the database will take some time to copy), The following operation will create a full transactional copy:

CREATE DATABASE <name> AS COPY OF <original_name>

2. Find the status of your copy operation:

SELECT * FROM sys.dm_database_copies

3. Generate a bacpac file on the copied database, which isn't being used by anyone

4. Delete the copied database, and you'll have a working bacpac file

You can see the progress of the copy in their portal.

Hope it helps!

To know more about Azure, join Azure course today.

Thank you!!