How to take the .bacpac backup of a SQL Server database, If there are schema issues?

Question

I needed to take the .bacpac backup of a database, though there were schema issues such as unresolved references to other databases among other issues; these challenges prevented me from taking the backup using SSMS as well as Azure Data Studio.

Thanks, in advance.

Answer 1

To take a .bacpac backup of a SQL Server database with schema issues, you can use the SQLPackage utility. This utility can export a database schema and data to a BACPAC file, even if there are unresolved references to other databases among other issues. You can then import the BACPAC file into Azure SQL Database or Azure SQL Managed Instance using the Import Data-tier Application Wizard in SQL Server Management Studio or the SQL Server Import and Export Wizard.

Here is an example command to export a database to a BACPAC file using SQLPackage:

sqlpackage.exe /Action:Export /ssn:<serverName> /sdn:<databaseName> /tf:<filePath>

Replace <serverName> with the name of the SQL Server instance, <databaseName> with the name of the database, and <filePath> with the path and file name for the BACPAC file.

---

References:

• Export to a BACPAC file by using the SQLPackage utility
• Import Data-tier Application Wizard in SQL Server Management Studio
• SQL Server Import and Export Wizard

Answer 2

There is no way to get .bacpac of the database as is, if there are schema issues.

Possible workarounds are:

• You may generate scripts to recreate the database. Instead of relying on .bacpac for restoration.
• Inactivate the database objects that are causing errors, by commenting out portions of the code, and activate them later, as needed.