Compare-DbaDbSchema

Synopsis

Compares the schema of a DACPAC file against a target database or DACPAC file using sqlpackage.

Description

Uses sqlpackage’s DeployReport action to compare a source DACPAC against a target (live database or DACPAC file) and returns a structured list of schema differences.

The source must be a DACPAC file. The target can be either a live SQL Server database or another DACPAC file.

Note: Comparing two live databases is not supported by sqlpackage. To compare two live databases, first export one as a DACPAC using Export-DbaDacPackage, then pass that DACPAC as the source to this command.

sqlpackage must be available. Install it via Install-DbaSqlPackage if needed.

Syntax

Compare-DbaDbSchema
    [-SourcePath] <String>
    [[-TargetSqlInstance] <DbaInstanceParameter>]
    [[-TargetSqlCredential] <PSCredential>]
    [[-TargetDatabase] <String>]
    [[-TargetPath] <String>]
    [[-OutputPath] <String>]
    [-KeepReport]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Compare-DbaDbSchema -SourcePath C:\temp\source.dacpac -TargetSqlInstance sql2019 -TargetDatabase AdventureWorks

Compares the source.dacpac schema against the AdventureWorks database on sql2019 and returns a list of differences.

Example: 2

PS C:\> Compare-DbaDbSchema -SourcePath C:\temp\v2.dacpac -TargetPath C:\temp\v1.dacpac

Compares two DACPAC files offline and returns the schema differences.

Example: 3

PS C:\> Export-DbaDacPackage -SqlInstance sql2016 -Database db_source -FilePath C:\temp\db_source.dacpac
PS C:\> Compare-DbaDbSchema -SourcePath C:\temp\db_source.dacpac -TargetSqlInstance sql2016 -TargetDatabase db_target

Exports a DACPAC from the source database, then compares it against the target database on the same instance.

Example: 4

PS C:\> Compare-DbaDbSchema -SourcePath C:\temp\source.dacpac -TargetSqlInstance sql2019 -TargetDatabase AdventureWorks -KeepReport -OutputPath C:\reports

Compares schema and keeps the XML report file in C:\reports.

Required Parameters

-SourcePath

The path to the source DACPAC file to compare from.

Optional Parameters

-TargetSqlInstance

The target SQL Server instance containing the database to compare against.

-TargetSqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Only SQL authentication is supported. When not specified, uses Trusted Authentication.

-TargetDatabase

The name of the target database on the target SQL Server instance to compare against.

-TargetPath

The path to the target DACPAC file to compare against. Use this for offline comparisons between two DACPAC files.

-OutputPath

The directory where the XML deployment report will be saved. Defaults to the configured DbatoolsExport path.
The report file is removed after parsing unless -KeepReport is specified.

-KeepReport

When specified, the generated XML deployment report file is kept after parsing. By default, the file is removed after processing.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

Outputs

PSCustomObject

Returns one object per schema difference found between source and target.

Properties:

• SourcePath: Full path to the source DACPAC file
• Target: The target database or DACPAC path
• Operation: The type of change (e.g., Create, Alter, Drop, Rename)
• Value: The schema object name (e.g., [dbo].[MyTable])
• Type: The object type (e.g., Table, Procedure, View)
• ReportPath: Full path to the XML deployment report (only present when -KeepReport is specified)