SQL azure restore bacap file error

Question

Dear all,

We have a database hosted in azure where we get runing backups.
We have extract a bacap file from our azure database in order to restore that backup onto the same server but as a new database name for different test to be run.

We notice that during the restore operation it all times failed, with following error :

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_ao_733371_event_parameter_event_id". The conflict occurred in database "db-az-prd-jira-2020-6-9-9-49", table "jiraschema.AO_733371_EVENT", column 'ID'.
Error SQL72045: Script execution error. The executed script:
PRINT N'Checking constraint: fk_ao_733371_event_parameter_event_id [jiraschema].[AO_733371_EVENT_PARAMETER]';

ALTER TABLE [jiraschema].[AO_733371_EVENT_PARAMETER] WITH CHECK CHECK CONSTRAINT [fk_ao_733371_event_parameter_event_id];

(Microsoft.SqlServer.Dac)

What we do not understand is that the database is not existing and we restore to create a fresh database from the bacap file.

Any reason why it is failling ?

regards
serge

Answer 1

Hi,

Please try out the below solution, its similar problem

https://stackoverflow.com/questions/17575459/unable-to-restore-bacpac-due-to-foreign-key-conflict

Answer 2

Good day @scal74,

Note: I cannot publish a full explanation since the QnA has a bug which prevent publishing answers which are not very short. I will split my short answer and put some part in the comment.

Before discussion the error and what you can do, I have to clarify something since it seems like you have huge misconception about what bacpac is or isn't.

bacpak has nothing to do with restoring a database or with DR (Disaster Recovery) solution! You cannot restore a database using bacpac.

A DR solution provides a way to return to exact point-in-time from all aspects. For example, if you use backup and restore then you restored database will be exactly as it was in the point-of-time of the backup. If at that time the database was corrupted then the restored database will be the same with the same corruption, and if at the time of the backup there were uncommitted transactions in the log file then these will be in the backup file and will be restored...

A bacpac is a copy of the schema and data, and nothing else. It does not take us to the point-in-time but provide simple information only about the schema and data in the time we created the file (and only the entities which we selected to store their data)

Answer 3

@ scal74 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.

I also suggest you to check the known error database - here is some info on KEDB in ITIL, for instance.

Answer 4

Dear all, thanks for your reply.
Sorry for my vocab used in that post. I clearly understand the way backup works.

In my scenario in fact we have a staging environement that is used simply for dev or testing.
Then at a given time we need to get fresh data from the production database, this is for that purppose only that we use bacap files. Simply to get at a given time prod data..

I think we point out what the issue trouble is :
The bacap file has been generated from a db which was in used and as bacap file are not transaction based, it might be we get some corrupted indexes at some points.

regards