Question: I've looked around the Wordpress forums about this and didn't find anything so I thought I might try here.

If you have a staging/dev Wordpress setup used for testing new pluging and such, how do you go about migrating the data in the staging database back to the production database? Is there a "Wordpress best practices" way to do this, or am I limited to having to manually migrate tables from one database to the other?

Answer: 
I have a script that mysqldumps a copy of my production Wordpress DB, restores it over my test Wordpress install & then corrects all the "production" settings & urls in the test DB.

Both my production & test databases live on the same server, but you could change the mysqldump settings to dump from a remote mysql server & restore to a local server quite easily.

Here are my scripts:

**overwrite\_test.coach\_db\_with\_coache\_db.sh**

```
#!/bin/bash 
dbUser="co*******"
dbPassword="*****"
dbSource="coach_production"
dbDest="coach_test"
tmpDumpFile="/tmp/$dbSource.sql"

mysqldump --add-drop-table --extended-insert --user=$dbUser --password=$dbPassword --routines --result-file=$tmpDumpFile $dbSource
mysql --user=$dbUser --password=$dbPassword $dbDest < $tmpDumpFile
mysql --user=$dbUser --password=$dbPassword $dbDest < /AdminScripts/change_coach_to_test.coach.sql

```

**change\_coach\_to\_test.coach.sql**

```
-- Change all db references from @oldDomain to @newDomain

SET @oldDomain = 'coach.co.za';
SET @newDomain = 'test.coach.co.za';
SET @testUsersPassword = 'password';

UPDATE `wp_1_options` SET `option_value` = REPLACE(`option_value`,@oldDomain,@newDomain) WHERE `option_name` IN ('siteurl','home','fileupload_url');
UPDATE `wp_1_posts` SET `post_content` = REPLACE(`post_content`,@oldDomain,@newDomain);
UPDATE `wp_1_posts` SET `guid` = REPLACE(`guid`,@oldDomain,@newDomain);
UPDATE `wp_blogs` SET `domain` = @newDomain WHERE `domain` = @oldDomain;
UPDATE `wp_users` SET `user_pass` = MD5( @testUsersPassword );

-- Only valid for main wpmu site
UPDATE `wp_site` SET `domain` = @newDomain WHERE `domain` = @oldDomain;

```
You need to handle the serialized objects. Here is a [client side HTML5 utility](http://petesaia.com/work/peach/) to handle it. Because it is all javascript, it's quite fast.

The alternative would be hooking a bash script into your deployment. So once the site is deployed, the db is backed up and deserialized with the new domain.