Database Deployment Strategies (SQL Server)

For this very problem I chose to use a migration tool: Migratordotnet.

With migrations (in any tool) you have a simple class used to perform your changes and undo them. Here's an example:

[Migration(62)]public class _62_add_date_created_column : Migration{    public void Up()    {       //add it nullable       Database.AddColumn("Customers", new Column("DateCreated", DateTime) );       //seed it with data       Database.Execute("update Customers set DateCreated = getdate()");       //add not-null constraint       Database.AddNotNullConstraint("Customers", "DateCreated");    }    public void Down()    {       Database.RemoveColumn("Customers", "DateCreated");    }}

This example shows how you can handle volatile updates, like adding a new not-null column to a table that has existing data. This can be automated easily, and you can easily go up and down between versions.

This has been a really valuable addition to our build, and has streamlined the process immensely.

I posted a comparison of the various migration frameworks in .NET here: http://benscheirman.com/2008/06/net-database-migration-tool-roundup

Read K.Scott Allen's series of posts on database versioning.
We built a tool for applying database scripts in a controlled manner based on the techniques he describes and it works well.
This could then be used as part of the continuous integration process with each test database having changes deployed to it when a commit is made to the URL you keep the database upgrade scripts in. I'd suggest having a baseline script and upgrade scripts so that you can always run a sequence of scripts to get a database from it's current version to the new state that is needed.
This does still require some process and discipline from the developers though (all changes need to be rolled into a new version of the base install script and a patch script).

We've been using SQL Compare from RedGate for a few years now:

http://www.red-gate.com/products/index.htm

The pro version has a command line interface that you could probably use to setup your deployment procedures.