Best practices to move a DB from Dev/ Test to Prod

What are the best practises to update/move a Database in Prod with the 开发者_运维知识库dev or the test machine copy?

I use RedGate's SQL Compare tool to keep the schema of 2 databases in sync. It's not cheap, but it's worth it if you are constantly pushing changes to production.

http://www.red-gate.com/products/sql-development/sql-compare/

I use the RedGate tools to create a script. Start wtih dev/test/prod all matching, then make the changes in dev. Compare dev to test with RedGate Compare and Data Compare (as needed), and let it make the scripts. I can then use the same script when moving the changes to prod.

For a free tool similar to RedGate's tool see this project: http://code.google.com/p/sql-dbdiff/

We use it to keep our dev and production databases in sync. Its a bit less flashy than RedGate's tool, but it works perfectly and you can't beat the price. Added bonus: You can get the source code and modify it yourself if you like.

For us, we move it via T-SQL statements (CREATE TABLE, etc.) We never copy an actual database and before we run the T-SQL on the production server, we have a review of the code.

Also, make sure that anything you do has the ability to roll back in case of issue.

Yes, you can create a DTS job to import from the dump on timely basis.

http://msdn.microsoft.com/en-us/library/cc917688.aspx