Creating .dacpac file from SQL Server and creating Database Project With Visual Studio

Creating .dacpac file from SQL Server and creating Database Project With Visual Studio

In this article, I am going to explain about SQL Server Database Project available in Visual Studio. I will cover the following points,

• What is .dacpac File
• Creating the .dacpac file
• Introduction of SQL Server Database Project.
• Create New SQL Server Database Project.
• Importing the .DACPAC file in Database Project.

Prerequisites

 Visual Studio and MS SQL Server/ Azure data studio.

 

What is Dacpac File  

A file with .dacpac which stands for for Data Tier AppliCation Package. This extension is a database file, created with Microsoft SQL Server data tier application, that contains the database model for representation of database objects. As it contains the complete model of the database, it is used to restore a database from the details available in the model. This will help inn database import and export operations. 

In simple word we can say data-tier application package in the form of window file containing all the data structure in a single unit.

What is the Purpose of the DACPAC file.

DACPAC helps the DBA and the developer to package   into a single unit . it will we handed over to the team member for deploying the database to the target environments in a manual ( hitting the target environment through visual studio / VS code) and automated manner (through azure pipeline or from GitHub pipeline).

How to create DACPAC file 

There are many options to create the DACPAC file but explaining 2 options from that :

• Use SSMS
• Use Visual Studio
• Azure data studio

Creating DACPAC using SSMS

SQL Server Management Studio is a easy tools through which you can generate DACPAC.

Open SSMS, right-click on database (in Object Explorer) and go to menu: Tasks -> Extract Data-tier Application 

For your reference adding the pictures also :-

Creating DACPAC from visual studio

it is the most best solution for creating the DACPAC file from Visual Studio we can change some configuration and create the dacpac file. Suppose we have some error in the database . some old Store procedure needs to be deleted using table old columns which was deleted recently . similarly Function, Views are using old column which was deleted recently. For those Scenario's we are taking the help of visual studio. 

By disabling that option you will avoid the issue and it doesn’t matter whether the database contains errors or not – the DAC package file will be generated.

Open extract window by using menu: View -> SQL Server Object Explorer, select required database and right-click on it and choice Extract Data-tier Application…

Creating DACPAC using Azure Data Studio

To create the DACPAC file from the azure data studio. Right-click the Databases folder or right-click a specific database in the Object Explorer. Then, select Data-tier Application Wizard

To create a new project and import existing database schema

To create a new project and import existing database schema

Click File àNew-à Project -à New Project dialog box à select SQL Server à SQL Server Database Project à Name the DataBase Project -àclick ok.

 

There is no specific platform for this project in previous versions of Visual Studio. You will be able to set your target platform in the Project Settings dialog box after the project has been created.

To Import the .DACPAC file into newly created Database

Right-click on Solution Explorer à Import à Database.

The Import Database dialog box opens. In the Source database connection section, click Choose a DACPAC file. The Import Database dialog shows a progress bar and displays a list of objects SSDT is importing. When the import operation has completed, click Finish to exit the final screen.

 

Examine the hierarchy in the Solution Explorer. Expand the dbo folder and you will find separate Functions, Tables and Views folders. Notice that the tables and function are grouped under their schema folders.

Now you can create any Tables, Store procedure , Function , View , Pre- development , Post -development scripts .. etc. from this database Project . After that push the code in the respective repository and push your changes in SQL database.

  

next Blog

https://ashissharma85.blogspot.com/2022/12/databases-changes-using-dacpac.html