waxingsatirical (2/13/2014)
Whilst I have found RedGate's SQLCompare tool useful for deploying schema changes in the past, the problem it has is that is a tool and will never be able deal with the level of complexity that a human being can. As with all script generating tools, eventually it comes across something that it can't do 100% safely so it ends up throwing a warning or doing something drastic like dropping and recreating a table.In my blog[/url], I've written about a method using Visual Studio Database Projects that combines user written scripts, for data and schema changes, with automated scripting of changes to programmability objects (stor procs, functions...).
The upside of this method is that it uses plain old visual studio solution files, so integrates seamlessly with Team Foundation Server. TFS has all sorts of funky Continuous Integration options to play with.
Warning: Hack alert.
Compare shouldn't drop tables and lose data. If it does, that's a bug. At least a warning should be there, though we can't prevent you from doing something silly.
We have added migration scripts, which are exactly what you say. This allows you to add your own scripts to fix those problems that can't be automated.
TFS has TFSBuild, which does CI.
Disclosure: I work for Red Gate.