Using SQL Compare filters with SQL Change Automation PowerShell module
Published 31 July 2019
A scripts folder can include a Filter.scpf file that contains SQL Compare filters. These filters exclude certain database objects when comparing schemas.
There are three SQL Change Automation cmdlets that use SQL Compare filters:
By default, this cmdlet will use the Filter.scpf file included in the source schema when generating the update script.
By default, this cmdlet will use the Filter.scpf file included in the source schema when updating the target schema.
Note: Unlike the above options, this cmdlet requires a filter specified via
-FilterPathparameter when cleaning out / creating a temporary database to validate the script folder schema.
The source schema must be either a scripts folder or a NuGet database package.
For any of these cmdlets, you can use the
-FilterPath parameter to specify an alternative filter file if you want to override the filter file generated by SQL Source Control in the source schema. You can also use this parameter to specify a filter file when none is included in the source schema.
Note that by default SQL Change Automation sets the
IncludeDependencies comparison option on by default when performing deployments of SQL Source Control projects. This helps ensure that objects are not deployed without necessary dependencies. It is worth noting that this option may take precedence over your filter settings.
$project = "C:\Work\scripts\" $test = New-DlmDatabaseConnection -ServerInstance "test01\sql2012" -Database "Test" -Username "sa" -Password "P@ssw0rd" $filter = "C:\Work\MyFilter.scpf" New-DatabaseReleaseArtifact -Target $test-Source $project -FilterPath $filter
This example demonstrates using the
MyFilter.scpf filter file to determine which database objects are included and excluded when the
New-DatabaseReleaseArtifact cmdlet generates the update script. This will override any filter file present in the SQL Source Control project.
Creating a filter file
A filter file can be created using SQL Compare.
To create a new filter file, see Using filters (SQL Compare documentation).