Gary Varga (2/13/2014)
Eric M Russell (2/13/2014)
The truth is that, for most development teams, there is only one instance of the development database, and every build is an "integrated" deployment, wether is should be or not....
In my experience, that WAS the case but many development teams have moved on to at least a development database and a Continuous Integration database.
Some others have individual development databases too, although that needs a slicker process to ensure that it is easy to keep the developers' local databases synchronised with the source code. Although this really needs tooling and configuration.
I don't even use a continuous integration or deployment tool. I hand code all my .SQL scripts and use TFS to maintain version control and execute them.
I typically have (1) script per object, which is versioned in source control. If the object doesn't exist, then the latest version of the object is created. If the object does exist, then a series of INFORMATION_SCHEMA queries and ALTER TABLE statements will intelligently add columns, alter data type, or whatever else is needed to bring the object up to the latest version. The script is basically a cumulative upgrade.
If a rollback is needed to a prior version, then I'll code a one-off incremental DDL script, but I don't bother pre-building rollback scripts for development; I only pre-build rollback scripts for production deployments.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho