Restore bacpac file to SQL Server 2014 results in error SQL72014

Question

I am trying to import a SQL Azure export file (.bacpac) to my local database and getting the following error.

Powershell command

PS C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin> .\SqlPackage.exe /a:import /sf:C:\SQLDATA\Backups\test-
2015-9-10-12-10.bacpac /tdn:test-live-local /tsn:.\sql2014

The error message below

Error importing database:Could not import package.

Error SQL72014: .Net SqlClient Data Provider:

Msg 547, Level 16, State 0, Line 3
The ALTER the FOREIGN KEY constraint "FK_CompanyPeopleCompany". The conflict occurred in database "dbo.Companies", column 'Id'.

Error SQL72045: Script execution error. The executed script: PRINT N'Checking constraint: FK_CompanyPeopleCompany [dbo].[CompanyPeoples]'; ALTER TABLE [dbo].[CompanyPeoples] WITH CHECK CHECK CONSTRAINT [FK_CompanyPeopleCompany];

I tried number of backups from live database and no success.

Any help will be appreciated.

Answer 1

When you generated the bacpac file, was it exported from a live database (where users were actively changing values)? Exporting from a live database can result in bacpac files that contain inconsistent data and cannot be successfully imported. This is because unlike backup files, bacpac files do not guarantee transactional consistency. The recommended process for creating a bacpac is to create a database copy in Azure (which guarantees transactional consistency), and then export from the unchanging copy.