Compare Azure SQL Database Schema Using sqlpackage

When working with Azure SQL Databases, it is quite common to have multiple environments such as Dev, Test, UAT, and Production. Over time, schema drift between these environments becomes inevitable. Before deploying changes to production, it is critical to understand exactly what is different and what will change.

In this article, we will see how to compare Azure SQL database schema using sqlpackage.exe and generate a delta script that shows the differences in a controlled and repeatable way. The approach and flow are similar to the DACPAC-based comparison strategy discussed earlier on AzureOps.

Why use sqlpackage for database comparison?

sqlpackage.exe is a command-line utility provided by Microsoft that works with DACPAC and BACPAC files. Using sqlpackage, you can:

• Compare two databases using DACPACs
• Generate a deployment (delta) script
• Control destructive changes explicitly
• Automate comparisons in CI/CD pipelines

For Azure SQL Database scenarios, this method is especially useful when direct schema comparison tools are not available or when automation is required.

Creating a DACPAC

To compare database schemas using sqlpackage, you first need a DACPAC file, which represents the database schema.

You can create a DACPAC using sqlpackage.exe, SSMS, or Visual Studio. For a quick, step-by-step guide covering all methods, refer to this article.

Installing sqlpackage Before You Begin

Before you can compare two Azure SQL database schemas using sqlpackage, you need to install the sqlpackage command-line utility on your machine. sqlpackage.exe is the same tool that Powers the DACPAC deployments discussed in this site’s previous article on deploying DACPACs to Azure SQL Database.

Here are the recommended ways to install sqlpackage, depending on your environment:

Option 1 — Install as a .NET Global Tool (Cross-Platform)

The easiest and most modern way to install sqlpackage on Windows, macOS, or Linux is using the .NET SDK tool installer. This gives you the sqlpackage command directly on your PATH. Run below command in command prompt.

dotnet tool install --global Microsoft.SqlPackage

If you already have it installed and want to update to the latest version, run:

dotnet tool update --global Microsoft.SqlPackage

This method works well for local development and automation scripts, including PowerShell and CI/CD pipelines.

Option 2 — Download Standalone Zip (Windows / macOS / Linux)

If you don’t use the .NET SDK or prefer a self-contained download, Microsoft also publishes standalone builds of sqlpackage:

1. Visit the official SqlPackage download page on Microsoft Docs.
2. Download the appropriate zip for your OS.
3. Extract and place the folder somewhere on your system (e.g., C:\tools\sqlpackage).
4. Add the folder containing sqlpackage.exe to your system PATH so you can call it from any terminal.

This method is useful if you want to avoid installing .NET tools or need to bundle sqlpackage with deployment scripts.

High-level approach

The comparison process consists of three simple steps:

1. Export both Azure SQL databases to DACPAC files
2. Use sqlpackage.exe with /Action:Script
3. Review the generated delta script before applying it

In this article, we focus on step 2, assuming you already have DACPAC files for both databases.

Sample scenario

Let us assume the following setup:

• Source (baseline) database: sales-test-db
• Target (to be aligned) database: sales-prod-db

Our goal is to compare Azure SQL Database schema and generate a SQL script that shows what changes are required to make Production match Test.

Sample sqlpackage command

Below is a sample sqlpackage.exe command to compare two DACPAC files and generate a delta script.

sqlpackage.exe ^
  /Action:Script ^
  /SourceFile:"C:\Dacpacs\sales-test-db.dacpac" ^
  /TargetFile:"C:\Dacpacs\sales-prod-db.dacpac" ^
  /TargetDatabaseName:"sales-prod-db" ^
  /OutputPath:"C:\Dacpacs\sales_test_vs_prod_delta.sql" ^
  /p:DropObjectsNotInSource=True

In case you want to use PowerShell ISE, you can use below script.

# Path to sqlpackage.exe
$sqlPackagePath = "C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe"

# DACPAC paths
$SourceDacpacPath = "C:\Dacpacs\sales-test-db.dacpac"
$TargetDacpacPath = "C:\Dacpacs\sales-prod-db.dacpac"

# Target database name
$TargetDatabaseName = "sales-prod-db"

# Output script path
$OutputScriptPath = "C:\Dacpacs\sales_test_vs_prod_delta.sql"

# Publish options
$DropObjectsNotInSource = "True"

# Execute sqlpackage
& $sqlPackagePath `
    /Action:Script `
    /SourceFile:$SourceDacpacPath `
    /TargetFile:$TargetDacpacPath `
    /TargetDatabaseName:$TargetDatabaseName `
    /OutputPath:$OutputScriptPath `
    /p:DropObjectsNotInSource=$DropObjectsNotInSource

This command does not apply any changes. It only generates a SQL script that you can review and execute manually if needed.

Understanding each parameter

Let us break down all the parameters used in the command.

/Action:Script

Specifies that sqlpackage should generate a deployment script instead of publishing changes directly to a database. This is the safest option when comparing environments like Test and Production.

/SourceFile

Path to the source DACPAC file. This represents the desired state of the database.

/SourceFile:"C:\Dacpacs\sales-test-db.dacpac"

In our example, the Test database schema is treated as the source of truth.

/TargetFile

Path to the target DACPAC file. This represents the database you want to compare against.

/TargetFile:"C:\Dacpacs\sales-prod-db.dacpac"

The generated script will contain changes required to make the target match the source.

/TargetDatabaseName

Logical name of the target database.

/TargetDatabaseName:"sales-prod-db"

This parameter is mandatory for the Script action, even when you are comparing DACPAC-to-DACPAC.

/OutputPath

Specifies where the generated delta script should be written.

/OutputPath:"C:\Dacpacs\sales_test_vs_prod_delta.sql"

Always review this script carefully before executing it in Production.

/p:DropObjectsNotInSource

Controls whether objects that exist in the target but not in the source should be dropped.

/p:DropObjectsNotInSource=True

• True – Generates DROP statements for extra objects in the target
• False – Preserves objects that exist only in the target

⚠️ Use this option with caution, especially for Production databases.

Common additional publish parameters

Depending on your requirements, you may also consider these commonly used parameters:

• /p:BlockOnPossibleDataLoss=False
• /p:IgnorePermissions=True
• /p:IgnoreRoleMembership=True
• /p:ExcludeObjectTypes=Users;Logins
• /p:ExcludeSchemaValidation=True

These options help fine-tune the comparison and avoid unwanted differences.

Best practices

• Always generate a script first (/Action:Script)
• Review the delta script line by line
• Never auto-apply changes to Production without validation
• Store DACPACs and scripts in source control
• Use the same approach in CI/CD pipelines for consistency

Conclusion

Comparing two Azure SQL databases using sqlpackage.exe is a reliable and automation-friendly approach. By working with DACPAC files and generating delta scripts, you gain full visibility and control over schema changes before they reach Production.

This method fits well into modern DevOps workflows and provides a safe alternative to manual or GUI-based comparisons.

If you regularly manage schema changes across multiple environments, sqlpackage should be a core part of your database deployment tool.

Pro tips
1. If you want to generate BACPAC file of an Azure SQL database, you can refer to this post

See more

Kunal Rathi

With over 14 years 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.