Creating a CHANGE script in Management Studio?
sql, sql-server, sql-server-2008, ssms
Solution
Do not make changes to the test server using the UI. Write scripts and keep them under source control. You can test your scripts starting from backups of the live data and you can tune yoru scripts untill they achieve the desired result. Then you can check in the scripts for reference and later apply them on the live server. See this article Version Control and Your Database.
BTW, check out the SSMS toolpack, I think it may do what you want (I'm not sure). My advice stand none the less: version your schema, use explicitly created/saved scripts, use source control.
Problem
I was wondering if there is a way to automatically append to a script file all the changes I am making to my columns, tables, relationships etc... The thing is I am doing a lot of different changes on a TEST db and the idea will be to apply this change script when I move the test db to production... hence keeping production data but applying all schema and object changes. Is there an easy way to do this? Can it also migrate database diagram changes? I have seen how you can create a change script each time I do a change but this means I have to copy and paste into a master file. Actually pretty easy! I was just wondering if I was missing something?