Dacpac and Bacpac in SQL Server

When it comes to migrating a SQL Server database from one environment to another, admins tend to rely heavily on native SQL backup (BAK) files. It works, but also has a lot of problems. For instance, a BAK file created by a new version of SQL Server cannot be imported directly to a previous SQL Server version. Furthermore, the native backup (BAK) file contains the complete database (including all user data), so it poses a data privacy risk when you want to replicate a production database in a development environment. The Dacpac and Bacpac packages are an answer to the above problems.

Author
Recent Posts

Surender Kumar

Surender Kumar has more than twelve years of experience in server and network administration. His fields of interest are Windows servers, Active directory, PowerShell, web servers, networking, Linux, virtualization, and Kubernetes. He loves writing for his blog.

Latest posts by Surender Kumar (see all)

Kubernetes logs: Pod logs, container logs, Docker logs, kubelet logs, and master node logs - Mon, Sep 25 2023
Kubernetes DaemonSets - Wed, Sep 6 2023
Static Pods in Kubernetes - Fri, Sep 1 2023

What is a DAC package?

A data-tier application package (DACPAC) is essentially a self-contained compressed file with a *.dacpac extension that contains a schema of all database objects, such as tables, views, functions, and stored procedures. The key thing to note is that a DAC package, by default, doesn't include any user data, which makes it a perfect candidate for database replication between development and production environments.

Data owners can ensure the privacy of their data while extracting and handing out a DAC package from the production database to an external development team. Note that there are certain tools (e.g., SqlPackage) that allow you to include user data in a DAC package, but normally, a DAC package extracted with SSMS and Azure Data Studio is supposed to contain only the schema.

Another important use case for the DAC package is that you can upgrade an existing database. This is very useful when the development team changes the database schema, perhaps to add a new feature to the application. When rolling out the new feature to production, you can extract the DAC package from the DEV database instance and deploy it on the production database to upgrade the schema without affecting the existing data. Of course, you must be a member of the sysadmin, serveradmin, or dbcreator fixed server role to upgrade DAC. The process can be manual or automatic with the help of a continuous deployment (CD) pipeline.

What is a BAC package?

A backup package (Bacpac), on the other hand, contains everything, including schema and user data, making it an ideal choice for admins when it comes to database migration between different SQL Server instances. You can export a Bacpac package from the SQL Server instance running a higher version (e.g., SQL Server 2019) and import it to an instance running a lower version (e.g., SQL Server 2016) without any problem, which is not possible with the native BAK file approach.

A cool thing about both Dacpac and Bacpac packages is that you can use them with SQL Server instances running across different platforms, such as on-premises, Azure, AWS, or Google Cloud.

Data safety and security

This goes without saying, but since we are dealing with SQL Server databases in this post, you must perform a backup of your database. Furthermore, it is highly recommended to deploy (or restore) the Dacpac (or Bacpac) package in an isolated environment, particularly if you receive it from someone you do not trust. Since Dacpac and Bacpac are compressed packages, you can import and inspect their contents on an isolated developer machine running Visual Studio and SQL Server Developer Tools, as discussed here.

Working with a Dacpac package

Let's first discuss how to extract a Dacpac package and how to deploy it on a different SQL Server instance. You can do it with SQL Server Management Studio (SSMS), but we will use Azure Data Studio for this post. To learn the differences between SQL Server Management Studio and Azure Data Studio, check out this post.

For the demo, I will use two SQL Server instances:

SQL-PROD: Running SQL Server 2016
SQL-DEV: Running SQL Server 2019

You can see that the SQL Production instance is running an older version of SQL Server, whereas the Development instance is running a newer version.

Extract a Dacpac file

Suppose we are in the process of upgrading our application. We need to create an instance of the production database but without the actual user data. To do so, follow these steps:

First, launch Azure Data Studio and install the SQL Server Dacpac extension, as shown in the screenshot below:
Installing the SQL Server Dacpac extension in Azure Data Studio
Now connect to the Production SQL Server instance (SQL-PROD, in our case). You can see the COMPANY database with a few tables, stored procedures, and functions. The tables have some data.
Viewing the production SQL Server database using Azure Data Studio
To extract the DAC package, or, in simple words, to create a Dacpac file, right-click the database and select the Data-tier Application Wizard option.
Launch the Data tier Application Wizard in Azure Data Studio
If you don't see this wizard, make sure you have installed the SQL Server Dacpac extension, as shown in step 1.
The first step in the wizard shows four operations: Deploy Dacpac, Extract Dacpac, Import Bacpac, and Export Bacpac. All four options are pretty much self-explanatory, but the description that you see along with each option in the wizard makes it even clearer.At this point, we are interested in extracting a Dacpac file from the production database, so I will choose the second option and click the Next button.
Extract a data tier application from an instance of SQL Server to a _.dacpac file Extract Dacpac
Now, select the Dacpac settings, and click the Next button.
Selecting the Dacpac settings in Azure Data Studio
Finally, review the settings and click the Extract button.
Review settings and extract a Dacpac file from the SQL Server database
On the Tasks tab at the bottom, you can see the progress of the Dacpac extraction.
Viewing the progress of the Dacpac extraction process

Deploy a Dacpac file

Now that we have a Dacpac file extracted, you can hand it out to the development team or deploy it on the DEV SQL server instance yourself. To do that, follow these steps:

Launch the Azure Data Studio and install the SQL Server Dacpac extension, as discussed in the previous section.
Connect to the DEV SQL server instance (SQL-DEV, in our case).
Expand the Databases node. Notice that there is no COMPANY database here yet.
Connecting to the Development SQL Server instance in Azure Data Studio
Now right-click the Databases node and select the Data-tier Application Wizard option, as shown in the screenshot below:
Launch Data tier Application Wizard to deploy a Dacpac file in Azure Data Studio
Again, you will see the four options, but this time, we will select the first one (i.e., Deploy Dacpac), and click Next.
Deploy a data tier application .dacpac file to an instance of SQL Server Deploy Dacpac
Now, select the Dacpac settings, as shown in the screenshot:
Select Deploy Dacpac settings for the DEV database
The important thing here is that you need to select the New Database option under the Target Database field because we are creating a new database. When you are ready with the new feature to deploy the changes to production, you need to select the Upgrade Existing Database option here.
Finally, review the settings, and click the Deploy button.
Review the settings and deploy a Dacpac file to the SQL Server instance
When deployment is complete, refresh the Databases node, and you will see the new COMPANY database created on the DEV SQL Server instance.
View the database deployed on the DEV SQL Server instance

You can see that all the database objects are there, except the data. This was expected, since we used the Dacpac package. Your developers can now start working on the DEV instance, insert their test data, and make the necessary changes to the database schema. Once everything is ready to roll out to production, you will follow the same procedure to extract the Dacpac from the DEV SQL Server instance and upgrade the production database to merge the new schema changes.

Working with a Bacpac package

Now, let us assume a completely different scenario in which you need to migrate a SQL Server database from an Amazon RDS (or Azure SQL Server) instance to an on-premises SQL Server. If your on-premises server is running the same (or higher) version as that of the source SQL Server, you can use the old-school way, as discussed in this post. But what if the SQL Server version on the target server is older? In that case, if you try to restore a BAK file, you will get an error, as shown below:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 15.00.2101. That version is incompatible with this server, which is running version 13.00.6404. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)

Restore of database failed

The Bacbac package comes to the rescue in such a situation.

For the demo, I will use the Amazon RDS for SQL Server instance as a source and the SQL-PROD instance as a destination. Our goal is to move the COMPANY database from Amazon RDS, which is running a newer version of the SQL Server (i.e., SQL Server 2019), to an on-premises SQL Server instance, which is running an older version (i.e., SQL Server 2016). Remember, the Dacpac and Bacpac packages are supported, whether your SQL Server instance is on-premises, Azure, AWS, or Google Cloud.

Export a Bacpac file

To export the COMPANY database as a Bacpac package, launch the Azure Data Studio, and make sure the SQL Server Dacpac extension is installed.

Connect to the Amazon RDS for SQL Server instance where the COMPANY database is currently located.
Now expand the Databases folder, right-click the COMPANY database, and select Data-tier Application Wizard.
In step 1, select the fourth (Export Bacpac) option, and click Next.
Export the schema and data from a database to the logical _.bacpac file format Export Bacpac
Now, make sure the correct source server and database are selected. Also, select a path to export the Bacpac file and click Next.
Select the settings for Bacpac export
In step 3, review the summary, and click the Export button. You can monitor the status of the Bacpac export on the Tasks tab, as shown in the screenshot.
Viewing the status of the Bacpac export in Azure Data Studio

Import a Bacpac file

Once you have the Bacpac package, it can be imported to any SQL Server instance. As already warned, if you do not trust the Bacpac sender, I would strongly suggest importing it to an isolated SQL Server instance.

For the sake of this post, we will assume that we trust the sender, so we will go ahead and import it to the SQL-PROD instance straightaway. To do so, follow these steps:

Launch the Azure Data Studio, and make sure the SQL Server Dacpac extension is installed.
Connect to the SQL Server instance to which you want to import the Bacpac file.
Right-click the Databases folder and select the Data-tier Application Wizard.
In step 1, select the third (Import Bacpac) option, and click Next.
Create a database from a .bacpac file Import Bacpac
Now, browse and specify the path of the Bacpac file. Make sure the target server and new database name are correct, and then click Next.
Select the import settings for Bacpac
Finally, review the settings, and click Import. This will import the COMPANY database from the Bacpac file, including all the data. See the screenshot below for reference:
Viewing the database imported from the Bacpac file

You just restored a database to an on-premises SQL Server instance using a Bacpac file.

Conclusion

I only covered a few use cases of the Dacpac and Bacpac packages. Admins can use them in a variety of scenarios. In a nutshell, you can use Dacpac to extract/deploy the database schema across different SQL Server instances and Bacbac to export/import the full database, including user data. That is all for this post.