Aveek Das
Export Data Tier Applications in SQL Server Management Studio

An introduction to Data-Tier applications in SQL Server

April 29, 2020 by

In this article, I’m going to introduce the data-tier applications in SQL Server. As the official documentation from Microsoft says – “Data-tier applications in SQL Server are a logical entity that can be used to develop and manage most of the SQL Server objects like tables, views, stored procedures, functions etc. as a self-contained package“. Essentially, what that means is it is a component of SQL Server, using which we can develop, build, test and deploy databases for SQL Server just like we can do for any other web or desktop applications.

Data-tier applications in SQL Server were initially released around a decade back with SQL Server 2008 R2. The build of a data-tier application is a DACPAC file – Data-tier AppliCation PACkage, also known as a DAC file in short. A DAC is just another compressed zip folder that contains the model of the database that is being designed in XML format along with some other files necessary for the deployment. Using a DAC, developers can develop DDL scripts for each object in the database and these scripts will be built into a DAC file, which can later be used as a deployment utility to publish the databases to various SQL Server environments. This approach of deploying SQL Server databases has reduced the hassle of managing multiple migration scripts which in other cases the developers need to maintain for each version.

Create data-tier applications in SQL Server Management Studio (SSMS)

Developers and Database Administrators (DBAs) can easily create DACs using SQL Server Management Studio. This enables the users to extract the DAC file from an existing database and then perform other essential operations supported by the data-tier applications. You can follow the steps below in order to extract a data-tier application.

Right-click on the existing database for which you would like to create a data-tier application. Select Tasks and then select Extract Data-tier Application...

Extract Data Tier Applications in SQL Server

Figure 1 – Extract Data-tier Application in SQL Server Management Studio

The Extract Data-Tier Application page appears. Since this wizard will generate a DAC file, we will need to provide an application name which is basically the name of the DAC file that is going to be created and an optional description along with it.

Another important point to note here is that while extracting the data-tier applications in SQL Server, we can assign a version number for the DAC file. This helps us to maintain a version history of all the DAC files that we are going to create later. Finally, specify the location where are you going to create the DAC file. In this case, I’m going to store the DAC file under this directory – “C:\temp\WideWorldImportersDW.dacpac“. Click on Next once done.

Extract Data Tier Application Wizard

Figure 2 – Extract Data-tier Application Wizard

Click on Next in the following pages and finally click on Finish. The data-tier application will be generated in the path that you have specified earlier.

Building the Data Tier Application

Figure 3 – Building the Data-tier Application

Now, that the data-tier application has been created, let’s go ahead and see the contents within the DAC file.

DAC File Generated

Figure 4 – DAC File Generated

The DAC file is a simple compressed zip archive which can be opened by any zip extractor. Rename the extension of the DAC file from DACPAC to ZIP and extract the contents to the folder.

Extract ZIP Archive

Figure 5 – Extract ZIP Archive

Once you extract the contents of the DACPAC file, you’ll see there are four different XML files within it.

DACPAC Contents

Figure 6 – DACPAC Contents

These XML files do not contain any data from the database. The database is scripted out only with the model information. In order to also export data, there is another file which the data-tier application supports, known as BACPAC. This is essentially, a backup along with the DACPAC file that is generated.

Creating a BACPAC file in SQL Server Management Studio

The steps to create a BACPAC file is almost similar to that of creating a DACPAC. Instead of choosing Extract Data-Tier Application, in this case, you need to select the Export Data-tier Application.

Export Data Tier Applications in SQL Server Management Studio

Figure 7 – Export Data-tier Application in SQL Server Management Studio

Proceed to the Export Settings page by clicking on Next. On this page, you can see there are two tabs – Settings and Advanced. Here, you can specify where would you like the BACPAC file to be created. You also have an option on this page to save the BACPAC file directly to Microsoft Azure instead. In this case, I’m going to save it on my local directory itself.

Export Data Tier Application Settings

Figure 8 – Export Data-tier Application Settings

On the same page, there’s also a second tab – Advanced. This allows us to choose from a list table under all the schemas available in the database that we are trying to export. You can choose the tables for which the data needs to be generated in the BACPAC file. Let’s select all the tables for this example and click on Next and finally click on Finish.

Export Data Tier Application Advanced Setting

Figure 9 – Export Data-tier Application Advanced Setting

The operation might take a while based on the size of the data that is being exported. You can see a list of all the tables that have been exported in the BACPAC file. Once the operation is completed successfully, click Close.

Export BACPAC Operation

Figure 10 – Export BACPAC Operation

Like the DACPAC file, the BACPAC file is also a compressed zip archive which can be renamed into a ZIP extension and we can view the contents within. I have renamed the extension of the file to zip and extracted the contents.

BACPAC File Contents

Figure 11 – BACPAC File Contents

As you can see in the figure above, in addition to the contents of the DACPAC file, we have two new directories in this – “_rels” and “Data“. In the Data directory, you can see a directory has been created for all the tables that you’ve selected while exporting the BACPAC file.

BACPAC Data Directories

Figure 12 – BACPAC Data Directories

Also, if you open further, each of these data directories contains a BCP file for that table, which is essentially the BCP utility in the SQL Server to bulk copy these data back into the database when restored.

BACPAC File Contents

Figure 13 – BACPAC File Contents

Importing a data-tier application in SQL Server Management Studio

We can import the BACPAC file back to the SQL Server instance, and the database will be restored as-is on the target server. Since we can import a BACPAC file, then the data along with the schema will be restored on the server. You can follow the steps below to import a data-tier application in SQL Server.

Right-click on the Databases and select “Import Data-Tier Application…” from the context menu.

Import Data Tier Applications in SQL Server

Figure 14 – Import Data-tier Applications in SQL Server

On the next page that appears, provide the proper path of the BACPAC file and click on Next.

BACPAC File Location

Figure 15 – BACPAC File Location

In the Database Settings page that appears, you can choose the name of the database and the location where the data files should be created. By default, it will take the path of the SQL Server Default Data directory. Click Next and finally on Finish once done.

Database Settings

Figure 16 – Database Settings

The wizard will import the data-tier application and a database will be created once the process is completed successfully.

Import Completed for Data Tier Applications in SQL Server

Figure 17 – Import Completed

Conclusion

In this article, I have explained what data-tier applications in SQL Server are. I have also explained how to create a DACPAC and BACPAC using SQL Server Management Studio. DACPAC files are just a copy of the schema from the entire database without any data whereas, in the BACPAC file, data is included as well. In my upcoming articles for data-tier applications in SQL Server, I’ll explain how to start developing the same from scratch using Visual Studio and SQL Server Data Tools.

Aveek Das
Development

About Aveek Das

Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interest are SQL Server, SSIS, and SSAS. In his leisure, he likes to keep his skills sharp by writing articles on his blog and other forums. He is also an amateur photographer and hops around to do street photography. You can find him on LinkedIn View all posts by Aveek Das

1,808 Views