Notes on migrating to #Moodle 2 with ILP 2 plugin + pruning the DB & site files

Warning (!) These notes might not mean much if you are not involved with Moodle/LMS administration in some way. However, I have found it useful to read and discuss (see footer links) how others have migrated Moodle 1 sites to moodle 2, so have assembled these notes:

Background & overview

@StanmoreCollege started to use Moodle (1.7) in 2007 and it is now used by nearly every member of staff & full time student in some capacity. The broad aim was to prepare the site for another 5 years of intensive communication, learning & creative use. By creative, I mean I wanted the database, codebase & servers setup so the college could readily exploit new services & software that could enhance teaching & learning. For example integrating with other community resources (personal blogs, mnet, hubs…) & publishing to the community. Ideally we would have installed a fresh moodle 2 site & built the 300+ courses from scratch, but this would loose the user account #IDs which links to users (ILP 1.0) Personal Learning Plan data in moodle 1.9 (PLP) & could require returning students to update their profile… This would also take hundreds of hours of teachers time to rebuild the courses, so the compromise was to upgrade the existing moodle 1.9 site to 2.2, then migrate the required courses across to a fresh moodle 2.2 installation (backup & restore), but where certain data* for example the Moodle 1.9 mdl_user table… was migrated to the fresh 2.2 installation.

Site particulars, pruning & upgrade

The site to ugrade was moodle at 1.9+ with ~ 300+ courses to be migrated. In previous years a few courses have exceeded 600Mb when backed up. This has made the course backup & restoration slow, & not worked at all in some cases…For this reason I requested that teachers clean up their courses & delete unused files. I then only kept any files over 20mb in moodledata unless there was a good reason to do so, and did other things like check the length of filenames for files that students & staff had uploaded. One reason why you might want to do this is referred to here . In the end I just used the query below to find the moodledata folder URL e.g

that included these – whole sentence long – filenames, where you can simply ‘Rename’ files. Alternatively you could edit the query just to find these folders. You could also download the result of this query to a spreedsheet, so teachers can identify and shorten file names themselves

 SELECT c.fullname, CONCAT('
 /course/modedit.php?update=', AS cmID,, 
 SUBSTRING_INDEX( r.reference, '/' , -1 ) AS FileName,, r.type, r.reference, r.timemodified,cm.course
 FROM mdl_resource r
 JOIN mdl_course_modules cm ON = cm.instance
 JOIN mdl_course c ON cm.course =
 WHERE LENGTH( SUBSTRING_INDEX( r.reference, '/' , -1 ) ) >100
 AND r.reference NOT LIKE 'http%'
 AND r.type <> 'html'
 ORDER BY cm.course DESC ,LENGTH( FileName ) DESC

So after pruning these user files etc. I deleted logs & stats rows from the database (These would be available in yearly archive sites we create @StanmoreCollege should they be need) I transferred the moodle site from a Windows server to a Linux server & performed a command line upgrade . This was so the user files could be processed & migrated into the new way moodle 2 handles & stores files . Below are some more notes on this upgrade:

Modules upgrade notes:

  • ILP (Personal Learning Plans etc) –> inserted ILP 1.0 tables into Moodle 2.2 DB…
  • Book (used for schemes of work) –> installed this mod in moodle 2.0 & 2.1 (becomes native in later versions of Moodle)
  • Turnitin Assignment type –> Deleted all instances of this prior to migration due to issues!
  • Turnitintool (plagiarism detection) –> installed mod in moodle 2
  • Feedback –> Did not migrate data for this (its in the Archive sites anyway)
  • Lightbox gallery –> Did not migrate data for this but reinstalled module later from github

Miscellaneous upgrade notes:

  • Additional 1.9 roles created: Manager, Tutor, Senior tutor, Manual-student were not migrated
  • compared upgrade with fresh DB schemas – used collation utf8_unicode_ci
  • Made changes to grade scales that I wanted to see in the new moodle 2 site

Set up of fresh Moodle 2.2 installation

Thousands of the mdl_user rows from the upgraded table were inserted into this fresh installation. This was necessary because I wanted all returning students to be able to login without requiring them to update their moodle profiles. Also for their ILP 1.0 data to be available.

*other considerations for this fresh moodle 2.2 installation were the password salt, Guest & Admin (ID#2) accounts, user mnethost value, updating links that pointed to our old Moodle site address … Also if you are migrating in this way, before you upload user pictures into your fresh Moodle 2 installation, remember to set picture=0 in the mdl_user table:

 UPDATE mdl_user u
 JOIN mdl_context ctx ON ctx.instanceid
 LEFT JOIN mdl_files f ON = f.contextid
 SET picture = '0'
 WHERE ctx.contextlevel =30
 AND u.picture = 1
 AND f.filearea IS NULL

Migration of courses to fresh Moodle 2.2 installation

This was a case of backing up courses in the site upgraded to moodle 2.2 then restoring them to the fresh Moodle 2.2 installation. Although this was time consuming it presented an opportunity for further pruning:

  • Unused activities & resources were not backed up & restored
  • Blocks were not backed up or restored (keeping things simpler)
  • Last year student user Grades were not restored
  • Courses that were no longer needed were not restored…

This resulted in reducing the DB & moodledata by well over half, which is good as Media & Computer teachers are now needing students to be able to upload files upto 50MB.

Related links:

Moodle 2.0: Making the Leap, Senate House, 29th February 2012

Submit comment

Allowed HTML tags: <a href="">google</a> <strong>bold</strong> <em>emphasized</em> <code>code</code> <blockquote>