We are now encountering an error with an automated process when attempting to restore a .BACPAC file using the sqlpackage utility but are successful when importing the same file using the "Import Data-Tier Application" wizard through SSMS.
The error we are getting is the following:
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_xx_xx". The conflict occurred in database "TEMP_xxx_5182022", table "dbo.xxx", column 'xxx'.
The automated process exports the .BACPAC file from a copy of a database running on an Azure SQL Managed Instance. The .BACPAC file then gets imported into SQL Server 2017 using sqlpackage.exe /a:Import.
Doing some further research, it sounds like this process will not succeed 100% of the time based on the fact that the exported file does not guarantee transactional consistency. The correct approach would be to generate a COPY ONLY of the DB and export which would require us to make some TDE encryption on that server.
When importing the .BACPAC through the Import Data Tier Wizard using SSMS, we are not encountering that error.
Does the wizard use the sqlpackage.exe utility or something else?
Are there any missing parameters we could pass to either /Import or /Export that could allow for a successful import? (i.e. /p:DisableIndexesForDataPhase)
Do we have any other options besides using the COPY ONLY method?
The issue here is the size of the DB being restored.
When SQLPackage.exe is used, you have to 2 options to specify the Target DB