question

scal74 avatar image
scal74 asked ·

SQL azure restore bacap file error

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

azure-sql-databaseazure-backup
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MalleswarReddy avatar image
MalleswarReddy answered ·
1 comment Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is exactly the case and source of our trouble
Seems to have an impact on the process.

thanks

0 Votes 0 · ·
pituach avatar image
pituach answered ·

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)


1 comment Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry for the inconvenience. The forum is still in preview state and it does not allow me to publish even the short version of the answer. I attach the answer which I tried to publish in the text file: 9746-qna-34473.txt

I hope this bug will be fixed soon ��

This is a known bug and on the top of the forum you can see a message with the title "Access Denied error when posting content", which provide the details on the bug



0 Votes 0 · ·
qna-34473.txt (3.5 KiB)
scal74 avatar image
scal74 answered ·

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

1 comment Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You are most welcome :-)

I clearly understand the way backup works.

Please remember that in the forum we have no idea who ask the question and what is his background + the answers are not only for the person who ask the question but also for any future users which will have the same issue in the future and will arrive here from search engine for example. Therefore we might add some information which seems as something that is already clear to you, but if you keep read then usually you will also find the solution which fit exactly your case.

Notice the second option I mentioned which is editing the bacpac directly instead of editing the original database or create a copy only for the sake of creating the bacpak.


0 Votes 0 · ·