Automate DACPAC Creation and Setup Scripts for Database Development (2024)

By: Haroon Ashraf |Comments | Related: > Backup

Problem

As a SQL Server database developer, I would like to automate the availability of a DACPACand a setup script of a sample database so that any changes to the sample databasewill automatically change the DACPAC and the setup script for the sample database.

Solution

One of the possible solutions is to use a declarative database development toolsuch as SQL Database Project to create and manage the database structure and configuringthe debug output of the project to a desired accessible folder which contains thelatest version of the DACPAC and setup script for the sample database.

Why You Should Automate DACPAC and Setup Script Availability

It is worth knowing why we need to automate the DACPAC and setup script availabilityin the first place, but before that it is also important to know why we need multiplemethods to create a sample database.

Setup Script and DACPAC

Database development testing often requires an in-house ready to use sample databasewhich must offer the following for quick creation of the database:

• DACPAC
• Setup script

Please remember that DACPAC is simply a package which contains the database structureand reference data (if required) while the setup script refers to the T-SQL script (file)used to create a sample database.

Both DACPAC and setup scripts can be used to create a database instantly, however,it is up to the database developer or tester to choose the method of preferencebased on the purpose.

Please refer to the tipCreating Multiple SQL Server DatabaseBackup Formats in SSDT - Part 1 for detailed information about makingthese backup types available manually.

Technical Writers and Setup Scripts

These backup types (DACPAC and setup script) not only help database developersand testers to build a sample database, but they can also be used by technical writersto support their T-SQL examples.

Impact of SQL Database Project Changes on DACPAC and Setup Script

A SQL Database Project by default creates a DACPAC as a result of a successfulBuild which is then needed to be manually copied to the desired DACPAC folderavailable to the database developers or testers from a source control perspective.

This may become a laborious task since any time there is minor change in thedatabase project it requires the existing DACPAC available to be reused to be replacedwith newer one.

The same is true for the setup script which does require SQL Database Project tobe configured to create such a script at the desired location or you have to manuallycreate one script copying the T-SQL behind the database objects defined by the SQLDatabase Project.

Pre-Requisites

Please have a look at the pre-requisites of this tip to understand and implementit in a better way.

T-SQL and SQL Server Data Tools (SSDT)

This tip assumes that the readers have a good understanding of T-SQL scripts alongwith basic know-how of creating and managing SQL Database Project through SQL ServerData Tools (SSDT) in Visual Studio.

In order to get familiar with SQL Database Project please refer to the tipDeveloping Similar Structured Multi-Customer Databases with SQL Server Data Tools(SSDT)

Git Source Control

This tip also assumes that the readers are familiar with basics of source controlparticularly Git source control and are comfortable to check their database objectsinto source control.

Planning the Solution

In order to implement the solution, we need to think about the existing and expectedscenario regarding creating a database through SQL Database Project.

The following things must be planned as part of the solution:

• Choosing Database Management Tool
• Choosing Source Control
• DACPAC and Setup Script Availability Challenges

Choosing Database Management Tool

We are using SQL Database Project to create and manage sample database and itsDACPAC and setup script availability for development testing and other uses.

Choosing Source Control

The Git source control is going to be used for this solution although any othercompatible source control can also serve the purpose.

DACPAC and Setup Script Availability Challenges

Those of you who regularly work with SQL Database Projects already know thatdebugging the database project does create a DACPAC file after a successful Build,but that DACPAC file needs to be manually copied over to the desired location forgeneral use in the context of source control (folder).

We need to find some way to ensure that the DACPAC is either automatically copiedover to the desired location or it is created at the desired location without exposingthe other important project files which must not be put under source control.

As far as setup script is concerned it has to be created manually by copyingthe database objects definitions (T-SQL) and make it available in the form of setupscript unless the SQL Database Project allows some way to do it.

This is illustrated as follows:

Automating DACPAC and Setup Script

Let's start working on the solution step by step in order to achieve the desiredobjective.

Create Azure DevOps Project using Git source control

The DACPAC and setup script has to be available for anyone who has access tothe resource to be reused that requires this SQL Database Project to be part ofthe source control.

Create an Azure DevOps project and name it Automating DACPAC and SetupScript Availability as follows:

Please note that I have chosen the visibility of the project asPrivate where the intention is to make the projectalong with DACPAC and setup script available internally while you can set it toPublic which means anyone can access it and theavailable DACPAC and setup script, but in that case please ensure that you are authorizedto do so and the project along with sample database DACPAC and setup script doesnot contain any sensitive reference data or object(s) which must not be shared withPublic.

Go to the Repo section and rename the repo for (ease of use) as WatchesV2-dacpac-setupscript:

Connect with DevOps Project

Open Visual Studio and click Manage Connections… as shownbelow:

Then connect to the Git repo called WatchesV2-dacpac-setupscriptunder Manage Connections as follows:

Create SQL Database Project

Create a SQL Database Project called WatchesV2 under a new SolutionWatchesV2 DACPAC Setup Script:

The blank SQL Database Project is ready now:

Create DACPAC and Setup Script Folder

Next, right click WatchesV2 project and click Add NewFolder and add new folder named DACPAC-Setup-Script whichis going to host the DACPAC and setup script of the sample database WatchesV2.

The folder is created instantly as follows:

Check DACPAC-Setup-Script Folder Contents

Please locate this folder using windows explorer which must be in the same repopath where the SQL Database Project and the solution is saved:

If you check the contents of the folder DACPAC-Setup-Script it is empty at themoment because the project build has not been processed.

Create WatchType Table

Let's now add a new table called WatchType to the SQL DatabaseProject using the following T-SQL code:

CREATE TABLE [dbo].[WatchType]( [WatchTypeId] INT NOT NULL IDENTITY(1,1), [Name] VARCHAR(50) NOT NULL, [Detail] VARCHAR(150) NOT NULL, CONSTRAINT [PK_WatchType] PRIMARY KEY ([WatchTypeId]))

Setup Target Platform

You can set your desired target platform such as SQL 2017, SQL 2016, SQL 2014,etc. under Project Settings in order to make sure your database is compatible withyour desired SQL Server version.

Build the Project

Please press F5 now to build the project:

Now using Windows Explorer go to the bin/debug folder of theproject to see the contents:

The DACPAC file has been successfully created and that is what we need to beavailable for general use in the desired folder which is going to be put under sourcecontrol since by default Git source control does not take into account bin/debugfolder contents and the reason behind this is beyond the scope of this tip.

Debug the Project

The next step is to debug the project by pressing F5 which is going to deploy thechanges to the debug database after creating a setup script in the same bin/debug folder:

If you view the bin/debug folder of the project you are going to notice a WatchesV2.sqlwhich is the setup script has been added to this folder as a result of successfulbuild and debug process.

Check DACPAC-Setup-Script Folder Contents

So, what is the issue when both DACPAC and setup script are created as a resultof successful build and debug?

Actually, the DACPAC-Setup-Script folder is still empty:

We need to manually copy the DACPAC and setup script from the bin/debug to this foldereach time there is a small change in the database project.

Configure Project Build Settings

Go to the Build settings and point Build output path to theDACPAC-Setup-Script folder replacing bin/debug and save the changes:

Debug the Project and Check DACPAC-Setup-Script Folder

View the desired folder where we want the DACPAC and setup script availabilityafter debugging the project and surprisingly it is not empty anymore, rather itcontains the required DACPAC and setup script for the sample database:

Put Work under Git Source Control

Please save all your changes along with SQL Database Project and its solutionto the Git source control by first checking the changes to be saved by clickingthe pen icon at the bottom bar of Visual Studio or pressing CTRL+ALT+F7 key:

Right click WachesV2.dll and click ignore this local item since we don’twant this file to be saved into source control.

Add initial commit as comment and then from the drop down selectCommit All and Push as shown below:

Check Azure DevOps

Go to Azure DevOps project and click Files from the left navigationbar and then locate the folder DACPAC-Setup-Script under project WatcheV2under solution WatchesV2 DACPAC Setup Script:

Congratulations, you have successfully automated the process of creating a databaseDACPAC and setup script availability for database developers and testers who haveaccess to this repository.

Next Steps

• Please create a test database WatchesV3 from the available DACPAC and seeif it is the exact copy of the sample database WatchesV2 or not
• Please Run the setup script by enabling SQLCMD and replacing WatchesV2 withWatchesV4 to create sample database WatchesV4 and see if itmatches with WatchesV2 debug database or not
• Please try to automate the tasks of creating BACPAC file.

About the author