Export and Import BACPAC files using SSMS

Jan 03, 2024

---

---

This guide describes how to export and import BACPAC files using Microsofts SQL Server Management Studio. Doing this is especially helpful to perform local backups or to setup a local development instance of an existing database.

Requirements

The following software must be present at your client machine:

• Microsoft SQL Server Management Studio. Get it here.

Other requirements are:

• You will need the connection string for your source MSSQL Server and your target MSSQL Server.

Exporting a database

Exporting the database can be done using the UI of SSMS.

• connect to your source MSSQL Server
• right-click the database you want to export and choose “Tasks”, “Export Data-Tier Application”.

• enter a file name for the new bacpac file using the “browse” button within the wizard. Then follow the wizard until completion. For a full export, no additional settings are required.

Import an existing bacpac file as a new database

• right-click the “Database” folder in your target SQL Server, then click on “Import Data-Tier Application”

• choose the previously exported file

• specify a new database name. Importing into an existing Database is not possible.

Notes on Azure SQL Databases

When exporting an Azure SQL Database and trying to import it into a local SQL Server (e.g. running in docker-compose), you need to make sure that the “Enabled Contained Databases” setting is set to TRUE in your SQL Server properties. This is neccessary because Databases within Azure SQL PaaS are isolated using the “contained database” feature. Microsoft writes this:

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.

Microsoft Documentation

Experience shows, that this settings is by default off, at least for Linux container instances of MSSQL Server.

You can check if this feature is enabled by following these steps:

• right-click your target MSSQL Server and choose “Properties”

• go to the advanced section and make sure the setting “Enable Contained Databases” under section “Containment” is set to TRUE

If you do not want to keep this setting enabled after import, you can change it back to FALSE as it is only needed for the import.

Sources

For further reading or more insights, here are the sources used for this guide.

• Microsoft documentation on how to import a bacpac, retrieved Jan 03, 2024
• Microsoft documentation on how to export a bacpac, retrieved Jan 03, 2024
• Microsoft documentation for Contained Databases, retrieved Jan 03, 2024
• Stackexchange Question on contained database imports, retrieved Jan 03, 2024

---