Is there a way in a visual studio database project to exclude certain object during deployment?

Question

I have a scenario where I am using a visual studio 2010 database project to deploy changes to multiple database installations, but on certain ones I want to exclude a set of views from that copy. Is there a way using build configurations to exclude these views from being deployed?

Answer 1

I ended up writing a custom deployment plan modifier, controlled using sqlcmd variables specifying objects that should not be touched during deployment. Each database project configuration may reference a different set of those variables (a .sqlcmdvars file) - this can be set on the Deploy tab in the project properties.

The plan modifier inspects the deployment plan and deletes steps that create/modify/delete objects which should be ignored. We are using it to ignore data files (which have different names on various staging environments), some backup tables and a few types of objects we do not keep in the DB project (users, role memberships, database level permissions). The functionality is similar (but finer-grained) to Schema Comparison configuration (ignored object types), but it works during deployment (also with VSDBCMD).

The "write a custom deployment plan modifier" part may sound like a lot of work, but actually it is quite simple, it took me less than a day, including the learning and testing parts. There is a very helpful walkthrough on MSDN.