Using comparison options with SQL Change Automation PowerShell module for SQL Source Control Projects
Published 21 May 2018
This only applies to SQL Source Control projects, to edit compare options for SQL Change Automation projects see Configuring comparison & script generation options
The following SQL Change Automation cmdlets use SQL Compare and SQL Data Compare to compare database schemas:
Invoke-DatabaseBuild
New-DatabaseDocumentation
Invoke-DatabaseTests
New-DatabaseReleaseArtifact
Sync-DatabaseSchema
SQL Compare options
SQL Compare has a range of options that allow you to configure what is compared between the schemas and modify certain other behaviours of the comparison.
The SQL Change Automation cmdlets use a set of sensible defaults for these options when they run a comparison. See Default SQL Compare options used by SQL Change Automation PowerShell module.
SQL Change Automation cmdlets ignore SQL Compare options specified in SQL Source Control
SQL Source Control also uses SQL Compare. The default options used by the SQL Change Automation cmdlets aren't the same as those used by SQL Compare.
If you specify SQL Compare options in SQL Source Control, these are not used by SQL Change Automation cmdlets.
Using the -SQLCompareOptions parameter
Use the -SQLCompareOptions
parameter with any of the cmdlets listed above to modify the comparison behaviour. You can turn off the default options or specify additional SQL Compare options.
Turning off a default option
Use -SQLCompareOptions
to specify a default option you want to turn off. The option must be preceded by a minus sign.
Example
$staging = New-DatabaseConnection -ServerInstance "staging01\sql2012" -Database "Staging" -Username "sa" -Password "P@ssw0rd" $test = New-DatabaseConnection -ServerInstance "test01\sql2012" -Database "Test" -Username "sa" -Password "P@ssw0rd" New-DatabaseReleaseArtifact -Source $test -Target $staging -SQLCompareOptions "-ForceColumnOrder"
In this example, the ForceColumnOrder
option is excluded from the SQL Compare options used by the New-DatabaseReleaseArtifact
cmdlet. This means that the cmdlet will no longer force table column order in the database schema update.
Adding additional options
Use -SQLCompareOptions
followed by a comma-delimited list of options to specify additional SQL Compare options.
Example
$staging = New-DatabaseConnection -ServerInstance "staging01\sql2012" -Database "Staging" -Username "sa" -Password "P@ssw0rd" $test = New-DatabaseConnection -ServerInstance "test01\sql2012" -Database "Test" -Username "sa" -Password "P@ssw0rd" $options = "IgnoreComments, ObjectExistenceChecks" New-DatabaseReleaseArtifact -Source $test -Target $staging -SQLCompareOptions $options
In this example, the IgnoreComments
and ObjectExistenceChecks
options are added to the default SQL Compare options that the New-DatabaseReleaseArtifact
cmdlet will use. You can use the alias rather the full option name, for example ic
for IgnoreComments
For a list of SQL Compare options, you can include, see Options used in the command line (SQL Compare documentation).
SQL Data Compare options
Similar to the way SQL Compare options can be used to fine-tune the schema comparison behaviour, SQL Data Compare options can be used to customize the static data comparison.
You can use -SQLDataCompareOptions
parameter with any of the cmdlets listed above to modify the static data comparison behaviour.
The default options are as follows:
- IgnoreSpaces
- IncludeIdentities
- DisableKeys
- OutputComments
- ReseedIdentity
- MissingFrom2AsInclude
Use -SQLDataCompareOptions
to specify a default option you want to turn off. The option must be preceded by a minus sign.
New-DatabaseReleaseArtifact -Source $test -Target $staging -SQLDataCompareOptions "-OutputComments"
Use -SQLDataCompareOptions
followed by a comma-delimited list of options to specify additional SQL Compare options.
New-DatabaseReleaseArtifact -Source $test -Target $staging -SQLDataCompareOptions "DoNotOutputCommentHeader"
For more information about data comparison options, see Options used in the command line (SQL Data Compare documentation)