SQL Server 2008 development and versioning?

I have a live SQL Server 2008 database and I need to start working on some changes in its structure. Obviously since it is live I want to have a dev copy I work on. How do I keep track of those changes开发者_开发技巧 so I can easily update my live DB when the time comes?

Big tick for Red Gate's SQL Source Control for this: http://www.troyhunt.com/2010/07/rocking-your-sql-source-control-world.html

Plus you can easily tie it into your CI process for automated releases: http://www.troyhunt.com/2011/02/automated-database-releases-with.html

Redgate tool's are excellent.

You can also use a Visual Studio database project to source control and synchronise schema and data, and deploy: Working with Database Projects

One option is to script all of you database schema changes in a sql file that you can run against the live database once development is done.

I recommend using IF NOT EXISTS checks so that your script is re-runnable without causing errors.

For Example:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
               WHERE TABLE_NAME='tablename') 
BEGIN
      CREATE TABLE...
END

This method will also allow you to script data conversions, if you have some refactoring of your database.