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.”
Tools in this post
You may also like
-
Article
How to use custom filters in SQL Compare
How to use filters in SQL Compare, when you don’t work with all the objects in a database.
-
Article
Deploying cross-database dependencies to different environments
The SQL Change Automation team here at Redgate occasionally take time out from development to explore some of the issues our customers face when automating deployment of database changes. As part of one such exercise, we took a closer look at cross-database and cross-server dependencies – these can cause problems when deploying databases to multiple environments
-
Article
Automatically keeping 'hotfix' and production database schemas in sync
How to use the SQL Compare Pro command line to automatically compare the schema of two versions of the same database, and then subsequently deploy to the target database any differences detected in the source database.
-
Article
Integrating SQL Server Tools into SQL Change Automation Deployments
Phil Factor shows how to integrate use of SQL Change Automation, SSMS registered servers, SMO, and BCP to automatically build or update a database on all servers in a group.
-
Forums
SQL Change Automation Forum
Continuous integration and automated deployments for your SQL Server database
