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...
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Figure 16 – Database Settings
The wizard will import the data-tier application and a database will be created once the process is completed successfully.
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.
He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.
In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn
View all posts by Aveek Das
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021