Forum Discussion
Importing bacpac to Azure database fails
I am trying to import a database from a bacpac file to our azure sql server. I am following these steps:
- Creating a bacpac file from our production database running on an MS SQL Server 2022.
- Importing it into azure using sqlpackage
But, it keeps failing with
*** An unexpected failure occurred: Data plan execution failed with message One or more errors occurred. (One or more errors occurred. (One or more errors occurred. (Violation of PRIMARY KEY constraint 'PK_ForexQuote'. Cannot insert duplicate key in object 'dbo.ForexQuote'. The duplicate key value is (1).
The statement has been terminated.))).
The ForexQuote table DOES NOT have any duplicate primary keys. If I omit this table in my bacpac export I get a similar error on another table.
I dont understand. What is going on here?
5 Replies
The error you're experiencing when trying to import the .bacpac file into Azure SQL Database—"Violation of PRIMARY KEY constraint"—is generally related to inconsistencies or issues with the data during the export/import process
When creating the .bacpac file, any uncommitted transactions could cause inconsistencies, which might manifest as primary key constraint violations. Ensure the database is in a read-only state or create the .bacpac file from a backup version of the database rather than directly from production to avoid any ongoing changes.Also sometimes, when the .bacpac is exported, changes in the data (due to active inserts/updates/deletes) can lead to data inconsistencies. To avoid concurrency issues, take a full backup of the production database and restore it to a staging environment before exporting the .bacpac file.
Make sure your target Azure SQL Database is set to a compatibility level that matches SQL Server 2022 as closely as possible
Check for Triggers or Constraints: Disable all triggers and constraints during the import process and re-enable them afterward, also check if the insert operations have any side effects due to these triggers.
verify the schema for each table and ensure it matches between the source and the target database. Look for foreign keys or relationships that may cause issues when certain rows are inserted first.Use the following options to ensure a more controlled import:
/p:IgnoreDuplicateKeys=true
/p:AllowDataTruncation=true(These options can help overcome issues with duplicate keys or data truncation during the import.)
Rebuild the indexes in your production database, then create a new .bacpac file
DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
If the error always appears on the same table, consider excluding that table and importing it separately after the rest of the database has been imported:
export the database as a .bacpac without the problematic table. Then, import that table separately using a different method like BCP or SQLBulkCopy
If possible, restore the production database backup on a staging server.
Run DBCC CHECKDB to confirm no corruption or errors exist in the database
Create a new .bacpac file from the staging server
Run sqlpackage with more verbose logging enabled to get more detailed information about the import process:
sqlpackage.exe /Action:Import /SourceFile:<path_to_bacpac> /TargetServerName:<server> /TargetDatabaseName:<dbname> /DiagnosticsFile:<logfile_path>
If the issue persists, you can try a manual export and import:
Instead of exporting the .bacpac, you can generate the schema and data scripts using SQL Server Management Studio (SSMS)
Right-click on the database > Tasks > Generate Scripts > select schema and data.
Note: This process may take time, but it allows you to have full control over each table's data and schema.
Create the target database schema in Azure SQL using the generated schema scripts.
Insert data manually using BCP or SQLBulkCopy while monitoring for any errors related to constraintsThanks!
I tried creating the bacpac file using ssms instead of sqlpackage and suddenly it worked. So, any ideas as to why the bacpac file created by ssms works better than the one created by sqlpackage? Do I need to add some argument or parameter to make sure that the export is compatible with azure sql server? I really want to use sqlpackage since i want to be able to script the entire process.
The difference you're experiencing between SSMS and SQLPackage when creating the .bacpac file is likely due to differences in how the tools handle the export process. SSMS has some built-in mechanisms that might better handle certain data integrity checks or constraints compared to sqlpackage
SSMS tends to be more forgiving with data consistency and has built-in safeguards to handle data dependencies and constraints more smoothly, especially for data being exported directly from a production environment.
SQLPackage, on the other hand, is a more direct, scriptable method and might not have the same inbuilt adjustments unless explicitly specified.
To get a similar level of reliability with SQLPackage, you can use specific parameters to ensure compatibility and avoid issues like primary key violations or other data integrity errors:Common Parameters to Use:
/p :
During export, it might be beneficial to ignore certain constraints temporarily and reapply them once the data is imported successfully.
/p:IgnoreConstraints=true
/p :This ensures that differences in versions (e.g., SQL Server 2022 vs. Azure SQL Database) are managed more smoothly.
/p:AllowIncompatiblePlatform=true
/p =False:If your database contains Full-Text Indexes, this can sometimes cause issues. Disabling this verification can prevent those errors.
/p:VerifyFullTextDocumentCount=false
/p :Ensure that database options are scripted during the export to make it compatible with the Azure environment.
/p:ScriptDatabaseOptions=true/p :
If the process is timing out during export or import, increase the command timeout.
/p:CommandTimeout=1200
The size of your database could also play a role. The SSMS interface might handle chunked data export and reconnections better compared to a command-line approach.Ideally, restore a copy of the production database to a staging environment. This ensures no active modifications are happening during the export.
Run sqlpackage with the parameters mentioned above to ensure better compatibility:
sqlpackage.exe /Action:Export /SourceServerName:<YourServerName> /SourceDatabaseName:<YourDatabaseName> /TargetFile:<YourTargetFile.bacpac> /p:IgnoreConstraints=true /p:AllowIncompatiblePlatform=true /p:VerifyFullTextDocumentCount=false /p:ScriptDatabaseOptions=true /p:CommandTimeout=1200
