How can I update my SQL Server database schema?

Question

Usually throughout development of a project I will deploy frequently, just to make sure I wont have any problems in production.

Also, throughout development, I find myself changing the database's schema.

How can I easily update the database in production?

I have been dropping the old database and reattaching the new one. Is there a faster way to update the deployment database?

Thanks

EDIT

What are some free tools for this?

Answer 1

Try DBSourceTools.
http://dbsourcetools.codeplex.com
Its open source, and will script an entire database
- tables, views, procs and data to disk, and then allow you to re-create that database through a deployment target.
It's specifically designed to help developers get their databases under source code control.