Using bacpac file is the most common procedure to migrate databases to/from Azure SQL Database, Azure Managed Instance, and SQL Server. A bacpac file can be created using tools like SSMS, SQL Azure Migration Wizard, and SSDT using Visual Studio, or directly executing the command-line utility SqlPackage.exe.

In many cases, problems arise while creating the new database from the bacpac file, even when the file was created without any error or alert. This can be related to implementing different criteria when we create a new bacpac file from these which are used during the import procedure, and it can be a result of incompatibility between the source and the target databases.

If an error is raised during the import procedure, which is related to a missing or not supported entity, then we can go back to the source database and re-create the bacpac without that entity. Unfortunately, if we have multiple issues with the bacpac file, then we might get alert only about one of these. Therefore, we might need to go over the issues one-by-one and fix the issues each time after we get information about another issue. This can lead to tens of iterations between attempting to create a new bacpac file (export) and attempting to create the database from the file (import).

In this session, we will examine the internals of the bacpac file, and we will go over some advanced troubleshooting. The bacpac file is a simple compressed ZIP file with the extension of "bacpac". It contains multiple files, which can store the information about schema and data of the SQL Server database.

Understanding the internals of the bacpac file allows us to edit the file manually, bypass incompatibility of the bacpac file to the target environment, change the target database imported entities, and prevent errors while migrating the database, as we will demonstrate in this session.

level 400