Create DACPAC from SQL Server Database

Azure, SQL Database, SQL Server
dacpac sql server database

DACPAC – Data-Tier Application Package is a zip file that contains a data model (database objects). This file is compatible with the (SQL Server data tools) SSDT database project. It is also used for SQL Server/Azure SQL database versioning and deployments using Azure DevOps CI-CD pipelines. DACPAC file is essential when migrating your on-premise SQL Server database to Azure. This is because Azure SQL database does not support regular SQL Server backups (.bak) files for restorations and requires a separate type of backup file called BAKPAC – Data-Tier Application Export (.bacpac file). Moreover, to create a BACPAC file, you must first create a DACPAC file to ensure that your database is ready for a BACPAC export. This article will show how to take a DACPAC extract of an on-premise SQL Server database.

Things to check before you create DACPAC from SQL Server Database

1. Database schema:

Ensure the schema is consistent and there are no unresolved issues or conflicts. Fully qualified object references in the procedure, function, view, etc., are not supported and should be removed.

2. Dependencies:

Check for any dependencies between objects in the database and ensure that all dependencies/ broken references are resolved before creating or deploying a DACPAC.

3. Common Language Runtime (CLR):

CLR functions are not supported in the Azure SQL database and should be removed before preparing for a DACPAC file from the SQL Server database.

4. Database Emails:

As Azure SQL database does not support cross-database queries with fully qualified names, it does not support database email procedure msdb.dbo.sp_send_dbmail. Hence, you should remove this.

5. Linked Servers:

Linked servers are not supported in the Azure SQL database, so linked server connections and references to objects with them should be removed.

Let’s Now DACPAC.

If you have checked and resolved all the above points, you can now create DACPAC from SQL Server Database. There are two ways to achieve this:

1. Using SQL Server Management Studio (SSMS):

This is the most straightforward way to create a DACPAC file from SQL Server database. Follow the below steps to do it.

  1. Right-click on the database in the object explorer and click Tasks and Extract Data-tier Application. This will launch a Wizard.
  2. Set the DAC properties as specified on the Wizard screen. And Click Next.
  3. If everything looks ok on the Validation and Summary screen, Click Next to start the DACPAC extraction process.

2. Using SQLPackage Utility:

This is the most flexible option for database operation. SqlPackage.exe is a command-line utility authored by Microsoft to perform various database operations like Extract, Export, Publish, etc.

Follow below simple steps to install the SQLPackage utility

  1. Download Sqlpackage .NET Core for Windows from here.
  2. Extract the zip file and store the content at your preferred location e.g., C:\Program Files\Microsoft SQL Server\150\DAC\bin. Create folders DAC\bin if it doesn’t exist already.

3. Add this path to the environment variable. Open the PowerShell ISE with the administrator and run the below command.

$SQLPackagePath = "C:\Program Files\Microsoft SQL Server0\DAC\bin"
# Location of the sqlpackage utility.

$envPath = [System.Environment]::GetEnvironmentVariable("Path", "Machine")
if (-not $envPath.Contains($SQLPackagePath))
{
    Write-Host "SQLPackage.exe path is not found. Updating the environment variable."
    $newPath = $envPath + ";" + $SQLPackagePath
    [System.Environment]::SetEnvironmentVariable("Path", $newPath, "Machine")
}

4. Restart PowerShell ISE.

5. Verify if you have correctly set up the Sqlpackage utility. Run the below command in the PowerShell ISE to check that.

sqlpackage -version

In case of an error with the above command, try the above steps again.

If you want to set up Sqlpackage.exe on platforms other than Windows, follow the instructions from this page.

$SourceServerName = "."
$SourceDatabaseName = "Sales"
$DacpacPath = "D:\dackpacs\"

SqlPackage /TargetFile:$DacpacPath$SourceDatabaseName".dacpac" /DiagnosticsFile:$DacpacPath$SourceDatabaseName".log" /Action:Extract /SourceServerName:$SourceServerName /SourceDatabaseName:$SourceDatabaseName

Completion of the above command will result in a .dacpac file. In case of any error during the operation, please check the .log file for error information.

How can I open the DACPAC file in Visual Studio?

You can refer to this post if you wish to create a database project from an existing DACPAC file.

Pro tips:
1. Sqlpackage.exe export has a lot of parameters. The above script has important ones. We can add more as needed. You can find a full list of parameters here. For example, the above script uses Windows authentication to connect to the source SQL server. To use SQL Server authentication, you must add /su:<username> /sp:<password> parameters.
2. If you run the script from the agent job, ensure the SQL Server Agent Service Account has adequate access to the source SQL server and target Windows folder locations for exporting files.
3. If you want to create a BACPAC file of SQL Server database, you can follow this article.

See more

Download Now
Kunal Rathi

With over a decade of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.
I am always interested in new challenges so if you need consulting help, reach me at kunalrathi55@gmail.com.

Shopping Cart
Scroll to Top