How to transfer changes from test environment to live site?

As with most other software it’s safer to try new plugins, configuration options or template changes in a separate testing environment, see e.g. WordPress local development environment.

Most questions and answers I’ve seen about this is the simpler case: doing the initial development locally and then deploy everything to the live site. That is easy. However, what if I need to test some change in the middle of site’s life cycle? I guess the steps should be:

1. Replicate a current state of the site to the testing environment
2. Test the changes in the testing environment
3. When done, merge the changes back to live

I know how to do 1+2 but am not sure about 3. This steps breaks down to 2 parts:

1. Files
2. Database

Again, 1 is easy so for example template updates are easily migrated from test to live. But 2 is tricky. Copying test db to live is not always possible (there might have been content changes in the meantime etc.), comparing schemas is also not a trivial task, theoretically there could be a plugin that would help with test <-> live synchronization but I’m not sure if it exists.

How do you people deal with this? Any tips & tricks or procedures?

I don’t think you will find any comprehensive or easy answer for this. I think the way you already broke down the various aspects of your installation is the key – i.e., thinking of changes to files as different from changes to the database.

Most of the changes I make are at the file level – e.g., CSS, adding code for a hook, stuff like that. They are isolated in files, as you said.

For the changes I make which affect the database, they tend to be compartmentalized as plugins, so they are also easy to isolate and deploy or rollback as long as I keep track of what steps I am taking.

Since you seem more concerned about your database changes, do you have a particular change or type of change that you want to discuss? You could consider doing a MySql dump of both the test and live database and then using a file diff tool to find the differences. In many cases this would probably let you extract a few SQL commands that could be packaged into a deployment script (but this could involve a fair amount of understanding about the underlying database to be sure to get the script right).