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?

Noleta

Answer 1

Have you tried creating a composite project? I haven't implemented this scenario myself but it would seem you could:

1. Create project1 which consists of the core objects required for all deployments.
2. Create project2 which consists of the set of views required for some deployments.
3. Add a reference to project1 in project2.

Thus when deploying project1, the views would not be included and when deploying project2, the views as well as the core objects would be included.

Have a look at the following Microsoft link. In particular the section titled "Uses and Limitations of Composite Projects"

http://msdn.microsoft.com/en-us/library/dd193405.aspx

Chishima

Answer 2

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.

Knockout