Restore deleted course

From ReservesDirect: Open Source EReserves System

When a course is accidentally deleted from a ReservesDirect system, it is usually not difficult to restore it from a backup database dump. First, you'll need to determine when the course was deleted. Look in the backup dumps of your production database and find the most recent dump that you know will contain the rows you're looking for.

Table of contents

1 Prerequisites 2 Create temporary database 3 Gather relevant information 4 Generate database dumps 5 Restore missing data

Prerequisites

1. MySQL database dump
2. Server where you can create MySQL databases
3. MySQL monitor

Create temporary database

From the backup server, use scp to copy the database dump to the machine where you'll be working (I usually use my Linux workstation for this). Fire up the MySQL monitor and create a database:

croddy@devbox ~ $ mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41 to server version: 5.0.11-beta-Debian_3-log
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 
mysql> CREATE DATABASE reserves_temp;

Query OK, 1 row affected (0.00 sec)
 
mysql> exit
Bye

Restore the dump from the production system into your temporary database (this will take a few minutes):

croddy@devbox ~ $ mysql -p reserves_temp < wed.sql
Enter password:
 
croddy@devbox ~ $

Gather relevant information

Next, you'll need to gather some information about the course from the database. It is likely that you've been given the department, course number and section, and the name of the instructor. These will most often be enough to find the course you need.

In these examples, we want to restore Philip Fry's History 385-006, Fall 2005, South Asian Politics Since 1945.

First we must find the course_id of the course we wish to restore:

 
mysql> SELECT * FROM departments WHERE departments.abbreviation="HIST";
+---------------+--------------+---------+------------+--------+
| department_id | abbreviation | name    | library_id | STATUS |
+---------------+--------------+---------+------------+--------+
|            48 | HIST         | History |          1 |   NULL |
+---------------+--------------+---------+------------+--------+
1 row IN SET (0.06 sec)
 
mysql> SELECT * FROM courses WHERE courses.course_number="385" AND courses.department_id=48 AND course_name LIKE "%south asian politics%";
+-----------+---------------+---------------+---------------------------------------------+---------------+--------+
| course_id | department_id | course_number | course_name                                 | uniform_title | old_id |
+-----------+---------------+---------------+---------------------------------------------+---------------+--------+
|      1051 |            48 | 385           | SOUTH ASIAN POLITICS SINCE 1945             | f             |   1260 |
|      4332 |            48 | 385           | South Asian Politics Since 1945 - Fall 2003 | f             |   7395 |
|      6290 |            48 | 385           | South Asian Politics Since 1945 - Fall 2005 | f             |   9409 |
+-----------+---------------+---------------+---------------------------------------------+---------------+--------+
3 rows IN SET (0.10 sec)

In this case, the course we want is course_id 6290, taught in Fall 2005. Make a note of the course_id.

Next, find the course_alias_id's you wish to restore. (It is often helpful to have a MySQL monitor open to your production database, so you can see easily which rows are missing)

mysql> SELECT * FROM course_aliases WHERE course_aliases.course_id=6290;
+-----------------+-----------+--------------------+---------+
| course_alias_id | course_id | course_instance_id | section |
+-----------------+-----------+--------------------+---------+
|            9699 |      6290 |               9699 | 004     |
|           12222 |      6290 |              11752 | 006     |
+-----------------+-----------+--------------------+---------+
2 rows IN SET (0.10 sec)

Make a note of the course_alias_id. In this case, 9699 still exists in production, so we will only restore the data from 12222. There is only one course instance to consider in this case, course_instance_id 11752. In some more complicated restorations, it may be necessary to query the course_instances table for more information.

• course_id: 6290
• course_alias_id: 12222
• course_instance_id: 11752

Generate database dumps

We will need to take dumps from five tables in order to completely restore a deleted course: courses, course_aliases, course_instances, reserves, and access (it is very, very unlikely that the items from a deleted course will have been removed).

In addition to the usual -p option, we'll pass three other arguments to mysqldump(1): -n, which will prevent CREATE DATABASE statements from being generated, -t, which will prevent CREATE TABLE statements from being generated, and -w, which will allow us to specify a WHERE clause to be used in selecting the rows for the dump.

mysqldump seems to generate more and more MySQL-specific code with each release. We're really only interested in the INSERT statements.

First, dump the rows for the courses table:

croddy@devbox ~ $ mysqldump -ntp reserves_temp courses -w 'course_id=6290'

INSERT INTO `courses` VALUES (6290,48,'385','South Asian Politics Since 1945 - Fall 2005','f',9409);

Next, the course_aliases table:

croddy@devbox ~ $ mysqldump -ntp reserves_temp course_aliases -w 'course_id=6290'

INSERT INTO `course_aliases` VALUES (9699,6290,9699,'004'),(12222,6290,11752,'006');

In this case, we've gotten two rows when we only need one. This will produce a 'duplicate row' error on the production system; either edit out the first row, or specify additional conditions in the WHERE clause to prevent this.

Now, the course_instances table:

croddy@devbox ~ $ mysqldump -ntp reserves_temp course_instances -w 'primary_course_alias_id=12222'

INSERT INTO `course_instances` VALUES (11752,12222,'FALL',2005,'2005-08-15','2005-12-31','ACTIVE','public');

Next, the access table:

croddy@devbox ~ $ mysqldump -ntp reserves_temp access -w 'alias_id=12222'

INSERT INTO `access` VALUES (52812,1147,12222,3);

And finally, the reserves table (this is usually very long):

croddy@devbox ~ $ mysqldump -ntp reserves_temp reserves -w 'course_instance_id=11752'

INSERT INTO `reserves` VALUES (203452,11752,267,'2005-08-15','2005-12-31','ACTIVE',0,'2005-08-09','2005-08-23'),
... 8< ... snip ... 8<  ...
(206373,11752,89840,'2005-08-15','2005-12-31','ACTIVE',0,'2005-08-23','2005-08-23');

Restore missing data

Lastly, take the snippets of the database dumps created above, and run them against the production database on the production system. If there are any duplicate row errors or column count mismatch errors, it may be necessary to go back and alter the table structure on the temporary database, edit the dump before applying it, or change the WHERE clause used to generate the dumps.