I'm very familiar with the process of exporting from Azure SQL V12 down to my dev box and then importing to my local sql (2014) instance. I'm spinning up a new Win10 box and have installed the SQL 2016 CTP. I'm connecting to that same Azure instance and can operate against it -- and can export a .bacpac just as with 2014.
But when I try to import to local I'm getting:
Could not import package.
Warning SQL72012: The object [FOO33_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
Warning SQL72012: The object [FOO33_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
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_dbo.Address_dbo.User_idUser". The conflict occurred in database "FOO33", table "dbo.User", column 'idUser'.
Error SQL72045: Script execution error. The executed script:
PRINT N'Checking constraint: FK_dbo.Address_dbo.User_idUser [dbo].[Address]';
ALTER TABLE [dbo].[Address] WITH CHECK CHECK CONSTRAINT [FK_dbo.Address_dbo.User_idUser];
To import from a BACPAC file into a new single database using the Azure portal, open the appropriate server page and then, on the toolbar, select Import database. Select the storage account and the container for the BACPAC file and then select the BACPAC file from which to import.
Import BACPAC File to On-Premise SQL Server : C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin> SqlPackage.exe /a:import /sf:\\Userdb0. bacpac /tsn:SERVER-SQL\DEV2016 /tdn:Azure_Test /p:CommandTimeout=2400.
Since this question was also asked and answered on MSDN, I will share here. https://social.msdn.microsoft.com/Forums/azure/en-US/0b025206-5ea4-4ecb-b475-c7fabdb6df64/cannot-import-sql-azure-bacpac-to-2016-ctp?forum=ssdsgetstarted
Text from linked answer:
I suspect what's going wrong here is that the export operation was performed using a DB instance that was changing while the export was on-going. This can cause the exported table data to be inconsistent because, unlike SQL Server's physical backup/restore, exports do not guarantee transactional consistency. Instead, they're essentially performed by connecting to each table in the database in turn and running select *. When a foreign key relationship exists between two tables and the read table data is inconsistent, it results in an error during import after the data is written to the database and the import code attempts to re-enable the foreign key. We suggest using the database copy mechanism (create database copyDb as copy of originalDb), which guarantees a copy with transactional consistency, and then exporting from the non-changing database copy.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With