Database Deployment and applying release patch using Database project in vs2010

Question

I want to know if we can limit the deployment script generated by the VS2010 database project so that it contains only the database objects that were modified and not the entire drop and create sequence.

For instance, after successive changes are incorporated, the next version script must only contain the stored procedures, functions or tables that were modified. I tried unchecking the "Always Recreate Database" option. However it just creates a script for entire database.

I have tried to find similar questions and I found some that are close to what I need, but still do not solve my problem.

Answer 1

We keep track of all changes by hand for preference and control reasons and keep a script that updates the database (I have an answer here on SO that outlines our steps), but we also use RedGate's SQL Packager, SQL Compare, and SQL Data Compare products for some of our needs.

They are pricey initially, but have more than paid for themselves in our org.

Answer 2

Database projects deploy by comparing the "compiled" project (the .dbschema file) against the database you're deploying to, and then creating a change script. There are many options about how the change script is created, but the basics are that a "live" change script is always created. It will only ever contain what the deployment tool believes is different.

Answer 3

The problem is that this simply does not work because it can not handle any type of rename of fields per definition. The wizard can not know whether a filed got renamed, or a filed dropped and an unrelated one generated. Also stuf like calculating (initial) values can not be handled.

So, this aproach simply is only feasible for degenerated edge cases and as such wont work long term on even medium size projects, sorry.