Drupal Post Migration Data Clean Up

Post migration data clean up, basically, most of the time when you develop a site you do it either under the default site of Drupal (sites/$new_url) or you do it under a different site (mynewsite.willhallonline.co.uk) with the intention of moving it to its own domain name when you have finished.

Moving your data when you have finished on your new site is pretty easy. As most of the parts of your data are in the database, you can almost move to any file location and providing it is in the drupal root you will be fine! Woo! However, there are a couple of quick SQL queries you can use to sucessfully move your site, especially if you are doing it within a drupal multi-site installation (and if you are running 3+ sites, maybe running through a multi-site is far easier).

Part 1: Updating the location of files in your database.

Your database stores the location of your files within the files database every time you upload a new file. This can be updated with this:

UPDATE `files` SET `filepath` = REPLACE(`filepath`, "sites/$new_url/files/", "sites/YOURDOMAIN/files/")

This will update your file locations for all downloads, imagecaches etc. Simple.

Part 2: Updating the location of embedded images/videos

Normally if you build pages using any kind of wysiwyg editor, you will have made pages with embedded images/videos etc. These are not controlled directly from the database so you will have to update these directly inside your node body and teasers.

UPDATE `node_revisions` SET `teaser` = REPLACE(`teaser`, "sites/$new_url/files/", "sites/YOURDOMAIN/files/")
UPDATE `node_revisions` SET `body` = REPLACE(`body`, "sites/$new_url/files/", "sites/YOURDOMAIN/files/")

(Obviously replace YOURDOMAIN with your domain!)