How to Add a Filter to an Existing SQL Change Automation Project
Kendra Little demonstrates how to create a SQL Compare filter file and save it to a SQL Change Automation project folder, so that it will be used automatically during development.
We recently got a question on the SQL Change Automation Forum: how do you modify an existing SQL Change Automation project to filter out objects?
At times there may be objects in your development database which you do not wish to import into version control. Filters are a great way to handle this. When you first set up a SQL Change Automation project in SQL Server Management Studio, there is an option in the graphic wizard to specify a filter file.
If you don’t specify the filter during setup, this seven-minute video shows how to add it in later and test it.
Embedded video: https://www.youtube.com/watch?v=Y56X9H4XG08
Video timeline
Don’t want to watch the whole thing? Here’s a summary with timestamps:
- 00:26 Recap of the question
- 01:00 The database and code used to reproduce the issue and test the filter
- 01:45 A view of the migrations being generated in SQL Change Automation in SSMS before the filter is applied
- 02:24 Opening SQL Compare to create, test, and save the filter file; part of the key here is that the file must be saved in the correct location and be named Filter.scpf
- 03:20 Details on configuring filters in SQL Compare
- 04:20 Digging into the filter pane in SQL Compare
- 05:10 Saving the filter file from SQL Compare and naming it Filter.scpf in the same folder as the .sqlproj file for the SQL Change Automation Project
- 06:00 Verifying that the filter is working in SQL Change Automation in SSMS by demonstrating that the migrations are no longer generated for the filtered objects
- 06:25 Showing the Version Control tab in SQL Change Automation in SSMS and verifying that the Filter.scpf file still needs to be committed
Want to learn more about SQL Compare filters?
This is just the beginning of what you can do with filters with Redgate’s solutions. For a different example of how filters can be used, check out the 46 minute video I recently recorded with Chris Unwin, “Reusable Schema Deployments with SQL Source Control and Octopus Deploy.”