SQL Change Automation 4

Using SQL Compare filters with SQL Change Automation PowerShell module

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:

  • New-DatabaseReleaseArtifact
    By default, this cmdlet will use the Filter.scpf file included in the source schema when generating the update script.
  • Sync-DatabaseSchema
    By default, this cmdlet will use the Filter.scpf file included in the source schema when updating the target schema.
  • Invoke-DatabaseBuild
    This cmdlet will use a filter specified via FilterPath 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.

Example:

$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 either be created using SQL Compare.

To create a new filter file, see Using filters (SQL Compare documentation).


Didn't find what you were looking for?