There was a question posted on the AUS-DOTNET mailing list a while back asking how people keep their SQL Server database schemas in sync between development and production servers. I reckon the tasks of:
a) keeping your test server up-to-date with production changes
b) rolling changes from test to production in a controlled manner
...would have to be one of the greatest needs for developers (and this problem just screams for a tool/utility to take care of it).
I've observed that some people have the solution of never using Enterprise Manager to do any design or make any changes and instead script everything; I use this solution myself for stored procedures and views so that I can also have file versioning (and because I use stored procs for a lot of business rules and logic so I'm able to continually tweak them and "roll back" changes if necessary by saving earlier versions). But unfortunately I'm addicted to Enterprise Manager for table design - it's too easy to use, I can enter the description meta-data right there and copy-and-paste columns between tables in design mode (Ent. Man. is also handy for permissions, relationships, database users, etc.)
To go along with my bad habit, I've been using SQL Compare for a couple of years now and it's indispensable. It's one of the 3 or so tools we've spent money on. I'm still on version 2 which goes back a couple of years but it gets the job done - point it at two databases, and it will script *all* the differences. There's also the SQL Data Compare product to synchronise rows.
I can't imagine having to do this stuff manually; trying to remember what I'd changed, where. Yeeech.
So I'm pumped that there's a version 5 coming up, and following advice on James' (one of the developers) blog post, I requested to be on the beta. We'll see how it goes.
Technorati tags: sql server, software, beta