question

scal74 avatar image
0 Votes"
scal74 asked Tamara2008-4327 answered

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
5 |1600 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
0 Votes"
MalleswarReddy answered scal74 commented
· 1
5 |1600 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
1 Vote"
pituach answered pituach edited

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
5 |1600 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
0 Votes"
scal74 answered pituach commented

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
5 |1600 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 ·
Tamara2008-4327 avatar image
1 Vote"
Tamara2008-4327 answered

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






5 |1600 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.