Unable to restore bacpac due to foreign key conflict

Question

I'm attempting to restore a backup (.bacpac) of a SQL Azure database to another SQL Azure database but am unable to do so because of 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 (I'll be attempting it again and will update with the exact error message shortly.

I've checked the live database and I can't see why this key is being violated.

I've also attempted to modify the model.xml contained in the .bacpac to remove the constraint but this fails because it now (rightly so) fails checksum validation.

Fidelity

I have had some weird behavior with this as well. Just last week I could not bacpac a SQL Azure database because of a missing Clustered Index. That is not even supposed to be possible in SQL Azure. – Camelback

I believe it's a defect in SQL data tools. I export/import my databases a dozen time a day no matter if I create a copy before exporting. The same .bacpac may import successfully a few times but fail two times more and it's totally random. – Ursa

Answer 1

The bacpac file is not transactional, so new rows written to your target database while the bacpac is being generated will end up corrupting the index. The database either must have no other users connected making writes, or you can copy the database and make a bacpac from the copy.

1) Copy the target database, which will return straight away, but the database will take some time to copy. This 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.

Stivers

Not looked at this for a while but this makes perfect sense. Thanks. – Fidelity

Well its kind of pointless to let you "Export" a copy while its in production then. – Goodhen

I used copy option form azure portal and when i run SELECT * FROM sys.dm_database_copies in query editor i get Failed to execute query. Error: Invalid object name 'sys.dm_database_copies'. ? – Applicant

Since the answer, Azures copy portal basically does this and you can see the progress of the copy in their portal. I think you need a specific kind of instance of SQL in Azure to have permissions to query sys directly. If you can make the copy + see progress in the portal, you don't need the run the commands. – Stivers