Alain Raap

Ken (and Jerry), here's another option I used for our messed up table mdl_backup_courses:

Save this query to an input file (for example query.sql), you can use your own timestamps for laststarttime, lastendtime and nextstarttime. Keep in mind that lasstarttime must be a timestamp before lastendtime and that nexstarttime must be the scheduled time of your automated backup settings (in my example 01:30).

use your-moodle-database-name;
select "TRUNCATE mdl_backup_courses;";
select "INSERT INTO mdl_backup_courses ( courseid, laststarttime, lastendtime, laststatus, nextstarttime) VALUES ( " , id , " , " ,  UNIX_TIMESTAMP('2019-03-05 08:05') ,"," , UNIX_TIMESTAMP('2019-03-05 08:10') , " , 3, ", UNIX_TIMESTAMP('2019-03-06 01:30'), ");" from mdl_course order by id;

The query generates an sql script that regenerates your mdl_backup_courses based on all the courses in mdl_course:

Run the query.sql on your database server with:

mysql -s -uyour-db-user -pyour-db-user-password  < query.sql ( > output.sql )

Example output.sql

TRUNCATE mdl_backup_courses;
INSERT INTO mdl_backup_courses ( courseid, laststarttime, lastendtime, laststatus, nextstarttime) VALUES (      1        ,      1551769500      ,       1551769800       , 3,   1551832200      );
INSERT INTO mdl_backup_courses ( courseid, laststarttime, lastendtime, laststatus, nextstarttime) VALUES (      2      ,      1551769500      ,       1551769800       , 3,   1551832200      );
INSERT INTO mdl_backup_courses ( courseid, laststarttime, lastendtime, laststatus, nextstarttime) VALUES (      3      ,      1551769500      ,       1551769800       , 3,   1551832200      );
INSERT INTO mdl_backup_courses ( courseid, laststarttime, lastendtime, laststatus, nextstarttime) VALUES (      4      ,      1551769500      ,       1551769800       , 3,   1551832200      );
...
...

Now load the output.sql with:

mysql -s -uyour-db-user -pyour-db-user-password  < output.sql

Your table mdl_backup_courses is recreated with the content of table mdl_course.

Ken, just thinking out loud, would it be possible to run a bash script against the automated backup php script?
I built a bash script that reads a file with course-ids, calls the backup cli script for each course-id  and that is
run via the Linux cron in the weekend. Only  that script doesn't look at the number of days of a course last
change in the parameters or the number of backups that must be present of each course. I'd like to find a
way to exclude the 'problem'  courses and to run the automated backup without the courses that make the run
fail each time.

This is the (simple) bash-script (file courses.txt contains the course-ids) to run a backup list outside Moodle cron;

#!/bin/bash
# ------------------------------------------------------------------------------
# Script: backup_moodle_courses.sh
#
# - Backup a list of courses with admin/cli/backup.php
#
# Writer:       Alain Raap
# Date:         24-01-2019
# ------------------------------------------------------------------------------
exec > >(tee -i /tmp/backup_moodle_courses.log)
exec 2>&1

# ------------------------------------------------------------------------------
# Set parameters
# ------------------------------------------------------------------------------
SCRIPT="backup_moodle_courses"
COURSES="/tmp/courses.txt"
COMMAND="$MOODLE_ROOT/admin/cli/backup.php"
PHP="$PHP_COMMAND_PATH"
BACKUP_PATH="/path-to-your-automated-backup-courses-map"

echo "${SCRIPT}: Start backup script"
# ------------------------------------------------------------------------------
# Read input file with course-id's to backup
# ------------------------------------------------------------------------------
while read COURSE_LINE ; do
   ID="$(echo ${COURSE_LINE} | cut -d',' -f1)";
   NAME="$(echo ${COURSE_LINE} | cut -d',' -f2)";
   echo "${SCRIPT}: Backup of course: " $ID " - " $NAME;
   echo "$PHP $COMMAND --courseid=${ID} --destination=${BACKUP_PATH}"
   $PHP $COMMAND --courseid=${ID} --destination=${BACKUP_PATH}
done < ${COURSES}

echo "${SCRIPT}: End backup script"
exit 0

Example courses.txt:

1500,Course A
1501,Course B
1502,Course C
1503,Course D

Thanks for thinking out loud Ken! I have the server where I truncated the mdl_backup_courses table up and running again and there the automated backup was running ‘normal’ again.

I did this in three steps:

- truncate mdl_backup_courses and wait until the table was recreated again with all the courses (laststatus for all courses = 5).

- I updated the table with laststatus = 1, start/endtime with a valid timestamp (endtime after starttime) and nextstarttime with the timestamp for the next nightrun

- the automated backup uses the parameters that are valid for the (nightly) batchrun 

This helped me to get it working again on one server (not the production server and with Moodle 3.5.4+!)

It’s not the ideal situation but a workaround to get the automated backup run back on track again.

Problem is the mismatch with the automated backup and courses that fail or take to long time to run. We can only try to exclude them manually from the ‘good’ courses. A bash script run by the Linux cron with the backup cli script was the only way to find out that a course was taking 7 hours for making a backup! These are the courses that cause a headache to administer this backup process.

Thanks for your tips and answers Ken. I'll think about what would be a good solution to make the automated backup
work good again. I already saw that an update of nextstarttime or truncate of the mdl_backup_courses could cause
serious problems with performance and a huge number of courses that would be backup-ed in a job of the
automated backup.

Do you use the automated backup task that runs every hour in the cron job of Moodle? We use this task too,
and I see that sometimes backups aren't finished when run during heavy workload of the site.

Maybe this subject could be worth overthinking by the developers how to handle these questions to organize your
backup strategy with a large number of courses and possible risks while scheduling a backup without causing
performance problems on your site. A mistake is made easily and you don't want to be called as administrator when
your site is not reachable anymore because of a heavy workload of your automated backup task that has run out of
scheduled time.

Ken , some more info for you about the mdl_backup_courses:

 select FROM_UNIXTIME(nextstarttime) as nextstarttime, count(id) as courses from mdl_backup_courses group by nextstarttime;

+---------------------+---------+
| nextstarttime       | courses |
+---------------------+---------+
| 2018-10-09 01:30:00 |     374 |
| 2018-10-17 01:30:00 |    1542 |
| 2018-10-29 01:30:00 |     119 |
| 2018-10-31 01:30:00 |       1 |
| 2018-11-04 01:30:00 |       1 |
| 2019-01-17 01:30:00 |       1 |
| 2019-02-09 01:30:00 |       1 |
| 2019-02-27 01:30:00 |    1730 |
+---------------------+---------+

Are the courses with a nextstarttime before 27-02 skipped? What happens when I would update all the rows and nextstarttime with the timestamp of tonight (2019-02-28 01:30:00) ?

 
select laststatus, count(laststatus) as number from mdl_backup_courses group by laststatus;
+------------+--------+
| laststatus | number |
+------------+--------+
| 1          |    285 |
| 2          |      6 |
| 3          |   3478 |
+------------+--------+

Six courses have an errror in the list.

select max(courseid) from mdl_backup_courses;
+---------------+
| max(courseid) |
+---------------+
|          7499 |
+---------------+

select max(id) from mdl_course;
+---------+
| max(id) |
+---------+
|    8017 |
+---------+

As you can see a lot of course id's  are missing in the mdl_backup_courses table (468 courses). New courses aren't
added to the mdl_backup_courses table anymore. Any idea why not?

What happens when I would truncate the table mdl_backup_courses? Will the automated backup not see the
backups that are already created? anymore