由于外键冲突，无法恢复bacpac [英] Unable to restore bacpac due to foreign key conflict

问题描述

我正在尝试将SQL Azure数据库的备份(.bacpac)还原到另一个SQL Azure数据库，但是由于以下错误而无法恢复:

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:

服务操作期间遇到错误.无法汇入 包裹.错误SQL72014:.Net SqlClient数据提供程序:消息547，级别 16，状态0，第3行ALTER TABLE语句与 外键约束"FK_x_xx".数据库中发生了冲突 "x"，表"dbo.x".错误SQL72045:脚本执行错误.这 执行脚本:PRINT N'检查约束:FK_x_xx [dbo].[x]';改变 表[dbo].[x]带有检查检查约束[FK_x_xx];

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];

我也曾尝试通过mscorlib异常在本地进行还原，但未成功(我将再次尝试进行该操作，并会在不久后显示确切的错误消息.

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.

我还试图修改.bacpac中包含的model.xml以删除约束，但这失败了，因为现在(正确的是)它无法通过校验和验证.

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.

推荐答案

bacpac文件不是事务性的，因此在生成bacpac时写入目标数据库的新行将最终破坏索引.数据库必须没有其他任何连接的用户进行写操作，或者您可以复制数据库并从该副本中创建一个bacpac.

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)复制目标数据库，该数据库将立即返回，但是复制该数据库将需要一些时间.此操作将创建完整的事务副本:

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)查找复制操作的状态:

2) Find the status of your copy operation:

SELECT * FROM sys.dm_database_copies

3)在复制的数据库上生成一个bacpac文件，任何人都不会使用它.

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

4)删除复制的数据库，您将拥有一个有效的bacpac文件.

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

这篇关于由于外键冲突，无法恢复bacpac的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！