When you manage Azure SQL Database across Dev, Test, UAT, and Production, schema drift can occur over time. Before promoting a database change, it is useful to see exactly what differs between two database schemas and generate the SQL required to synchronize them.
Quick answer: You can use SqlPackage to compare Azure SQL Database schemas by working with DACPAC files. Extract each database to a DACPAC, use /Action:Script to generate an incremental T-SQL deployment script, and review the generated changes before applying them. Microsoft also provides Schema Compare in SSMS, Visual Studio, Visual Studio Code, and command-line tooling.
What is a DACPAC?
A DACPAC is a database schema model that contains definitions for database objects. SqlPackage can extract a database into a DACPAC and use that model for deployment or comparison. By default, a DACPAC does not contain table data; data extraction requires additional options.
Why compare Azure SQL Database schemas with SqlPackage?
- Compare schema definitions between environments.
- Generate an incremental SQL deployment script.
- Review potential changes before modifying the target database.
- Automate schema comparison and deployment workflows.
- Use the same DACPAC-based process in CI/CD pipelines.
SqlPackage is Microsoft’s command-line utility for database portability and deployments on Windows, Linux, and macOS. Its Script action creates a T-SQL incremental update script that brings the target schema in line with the source schema.
Compare Azure SQL Database DACPACs: High-level process
- Extract the source Azure SQL Database to a DACPAC.
- Extract the target Azure SQL Database to another DACPAC.
- Run SqlPackage with
/Action:Script. - Review the generated SQL script.
- Apply the approved changes separately when you are ready to deploy.
This separates comparison and review from the actual deployment, which is useful when the target is a production database.
Install SqlPackage
The current SqlPackage documentation supports Windows, Linux, and macOS. You can install the CLI as a .NET global tool:
dotnet tool install --global Microsoft.SqlPackage
To update an existing global-tool installation:
dotnet tool update --global Microsoft.SqlPackage
Create DACPAC files from Azure SQL Database
Use the Extract action to create a schema DACPAC from each Azure SQL Database. For example:
SqlPackage /Action:Extract /SourceConnectionString:"Server=tcp:<server>.database.windows.net,1433;Initial Catalog=<database>;Authentication=Active Directory Interactive;Encrypt=True;TrustServerCertificate=False;" /TargetFile:"C:\Dacpacs\source.dacpac"
Run the same command for the target database and save it as a separate DACPAC. SqlPackage’s Extract action reverse-engineers the database schema into the DACPAC.
Compare two Azure SQL Database DACPACs
Assume the source is sales-test-db.dacpac and the target is sales-prod-db.dacpac. The source represents the desired schema and the target is the schema you want to compare against.
SqlPackage /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"
The Script action creates an incremental T-SQL script rather than applying the changes to the target database. The generated deployment plan can therefore be reviewed before deployment.
Important SqlPackage parameters
/Action:Script
Generates a T-SQL incremental update script that updates the target schema to match the source.
/SourceFile
Specifies the source DACPAC. The source is treated as the desired database model.
/TargetFile
Specifies the target DACPAC used for the comparison.
/TargetDatabaseName
Specifies the logical target database name used when generating the script.
/OutputPath
Specifies where the generated SQL script should be written.
Controlling potentially destructive changes
If the source and target schemas differ, the generated deployment script can include creating, altering, or dropping objects. One important property is:
/p:DropObjectsNotInSource=True
When enabled, objects that exist in the target but not in the source can be included as drop operations. Review this setting carefully before using it against Production.
PowerShell example
$sqlPackagePath = "C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe"
$SourceDacpacPath = "C:\Dacpacs\sales-test-db.dacpac"
$TargetDacpacPath = "C:\Dacpacs\sales-prod-db.dacpac"
$TargetDatabaseName = "sales-prod-db"
$OutputScriptPath = "C:\Dacpacs\sales_test_vs_prod_delta.sql"
& $sqlPackagePath /Action:Script /SourceFile:$SourceDacpacPath /TargetFile:$TargetDacpacPath /TargetDatabaseName:$TargetDatabaseName /OutputPath:$OutputScriptPath
Note: the hard-coded 160 path depends on the SqlPackage installation. If you install the current SqlPackage CLI as a .NET global tool, use the sqlpackage command directly instead.
SqlPackage vs Schema compare
SqlPackage is useful when you want a command-line and automation-friendly workflow. If you prefer an interactive comparison experience, Microsoft also provides Schema Compare. It can compare connected databases, DACPACs, and SQL database projects, show differences, selectively exclude changes, and generate or apply an update script.
| Requirement | SqlPackage | Schema Compare |
|---|---|---|
| Command-line automation | Yes | Available through command-line tooling |
| Compare DACPACs | Yes | Yes |
| Compare connected databases | Yes | Yes |
| Generate update script | Yes | Yes |
| Interactive review | Command-line/script | Yes |
Best practices for Azure SQL schema comparison
- Generate the script before applying changes.
- Review
DROPandALTERstatements carefully. - Keep DACPACs and generated scripts in source control where appropriate.
- Use the same process in CI/CD to make deployments repeatable.
- Validate the generated script against the target environment before production deployment.
Frequently asked questions
Can I compare two Azure SQL Database schemas using SqlPackage?
Yes. A common DACPAC-based workflow is to extract both databases and use the SqlPackage Script action to generate the schema changes required to align the target with the source.
Does a DACPAC contain database data?
By default, a DACPAC is a schema model and does not contain table data. If you need a database package containing schema and data, the BACPAC workflow is generally used.
Does SqlPackage change the target when using Script?
No. The Script action generates an incremental T-SQL script. The generated script must be reviewed and executed separately.
Can SqlPackage work without DACPAC files?
Yes. SqlPackage supports connected database sources and targets as well as DACPAC files. For an interactive comparison workflow, Microsoft’s Schema Compare tooling can also compare connected databases, DACPACs, and SQL database projects.
Conclusion
Comparing Azure SQL Database schemas with SqlPackage provides a repeatable way to identify differences and generate a deployment script. The DACPAC-based workflow is particularly useful when schema comparison needs to be incorporated into development, release, or CI/CD processes.
Pro tips:
1. Generate the script first and review it before applying changes to Production.
2. Treat DropObjectsNotInSource as a deliberate deployment decision because it can generate drop operations for objects missing from the source.
3. If you need schema and data together, use the BACPAC workflow rather than treating a standard DACPAC as a data backup.
See more
Microsoft documentation: SqlPackage CLI reference · SqlPackage Script action · Schema Compare in SSMS
Kunal Rathi
With over 15 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.
