What is the best procedure for merging work done on a development copy of a site to the live production copy? Often times there has been a lot of new content added to the site since development started on the newest features. And most additions to a site will involve database changes. So copying any new files is easy, but what about the database? How do you merge your changes with the existing production database without losing new content that was added since the last time you updated the production site? Are there any modules that help with this?
I've adopted basically two schools of thought here (a 3rd school of thought, doing database diffs, I won't discuss because the complexity is quite high).
1) Deploy by dropping the production database, and importing a mysqldump of the development database. Optionally, run a regex find/replace beforehand on any hard-coded absolute links which reference the dev URL in the SQL dump. After importing the dev db into prod, automatically run SQL statements (usually via script) afterwards to change any settings that are different for prod than dev (e.g., maybe you have in variables table some connection settings for connecting to external systems that you need to change to point at prod external systems instead of at the dev version).
- use node_export and features to export nodes/features to files
- Optionally (and hopefully) version control
- Load files on prod system
- Use drush or admin interface to load features
- Use drush delete-all or admin interface to delete all nodes of the types you want to import
- Use drush ne-import or the admin interface to import the nodes from the nodes file you exported.
One note, I would highly suggest adopting a standard workflow, where content goes one direction only. Either Dev -> Prod or Prod -> Dev (I prefer this one).
I've done this, and am doing this on some big systems, with fairly good results, but there will always be many ways to slice this apple, choose whichever way works best for you.
Dump databases of live site copy & development copy of site in SQL file (use same parameters & settings for both dumps).
Then, compare both SQL files using a small comparison tool ExamDiff. It'll display file differences side-by-side with different colors. You can directly jump to the differences also (without scrolling). Examine the differences & add/edit lines to SQL file of live site. Make sure there's no absolute path/URL of development environment in that file. That's done! Time to restore the database for live site.
Make your life easier: In first step, dump only those tables which are changed. For example, if you've edited a module in development copy which targets a separate table, dump only this table. If you're not sure about particular table, whole database dump is fine.