Merging database structure changes from one server to another.

views:

answers:

Merging database structure changes from one server to another.

I currently have a website with three builds: development, staging and production. Each of those has it's own MySQL database instance. Each of the instances has different data in it which should not change (orders).

My question is, if I made changes to the structure of the development database, is there an easy way to propogate those changes to staging and production without affecting the data?

Thanks.

+1 A:

I'm sure there is a better solution than the one I'll tell you over here... But until someone posts one... Here you go...

If you can script your database strcture (or you have them already scripted on your source control), you can compare the scripts side by side and then extract the differences to run them on the required database...

I'm sure there are tools out there that would do all that for you... But I can't recall any names and if they're free or not... I hope someone helps you more then :)

Mina Tickylum 2009-10-10 11:05:00

+2 A:

Just do all of your schema changes with scripts which you keep in source control. When you release code to staging, then you will ship with the build the scripts to update the schema, and use the same ones when you ship to production.

It's that easy.

Don't EVER manually hack the database schema. Test your migration and rollback scripts (making rollback scripts is a VERY good idea).

MarkR 2009-10-10 12:41:48

i have mostly used MySQL for production built applications(compared to small time work done at uni). the MySQL gui Sequel Pro is really good to keep a log of structure/schema changes. it logs every single query even when you make schema changes using its gui utils. so at the end of the day you can copy the log into source control. do you know if this is possible using SQL server?

Yash 2010-01-20 13:33:24

In practice you will want to make small, very careful schema changes to be reviewed by your colleagues and executed as part of a controlled release process; it is not necessary or desirable to use a GUI to generate the migration scripts, as they usually need hand-tweaking anyway. I have found that the scripts generated by MSSQL's GUI are not usually portable to another server as they typically have lots of hard-coded names of dynamically named objects such as constraints.

MarkR 2010-01-20 16:40:16

ansaurus

tags:

views:

answers:

Merging database structure changes from one server to another.

related questions