Dealing with schema changes between dev & prod

This may be a stupid way of doing things, but it's what my company does... We make a schema change in dev, which we must subsequently move into production with our latest builds. So we manually open up the production SQL 2008 database, using the designer to make relevant modifications, deploy new code and then hope for the best. Sometimes we forget to make a change on the prod schema, which can obviously lead to major headaches. So, surely there is a better way? Ideally we would like some free tools to help us both identify and deploy schema changes, but I am not aware that such things exist....

We use ASP.NET with VS2010 if that makes a dif开发者_运维问答ference.

I advocate for upgrade scripts. Always deploy every change, not matter how minor, as an upgrade script. Then, when moving to production, simply run the upgrade scripts from version N to version N+1. Effectively ban all the 'visual' database design/management tools.

There are various efforts in this direction, code first ORMs, database GDR project, diff based deployment and so on and so forth. Personally, I find the explicit upgrade script a much better alternative on the long run.

See Writing and Changing Database Code.

I believe Team Foundation Server has a way to store and release database schema changes with each build. Beyond that, I agree with Remus Rusanu. All changes must be scripted and placed in a version script that is released with each version of the appliation.