SQL query for Moodle to show space usage in Course Backup Area by course

MoodleCourseBackupArea.sql

SELECT mc.fullname AS CourseName, 
	ROUND((SUM(mf.filesize)/1024)/1024, 2) AS Storage_Used_MB
	
FROM mdl_files AS mf
LEFT JOIN mdl_context AS ctx ON mf.contextid = ctx.id
LEFT JOIN mdl_course AS mc ON ctx.instanceid = mc.id

WHERE mf.component = 'backup' 
AND mf.filearea = 'course'
AND NOT mf.filename = '.'

GROUP BY mf.contextid
ORDER BY Storage_Used_MB DESC

SELECT
c.id AS course_id,
c.fullname AS course_name,
SUM(f.filesize) AS total_backup_space
FROM
mdl_course c
JOIN
mdl_files f ON c.id = f.contextid
JOIN
mdl_backup_courses bc ON c.id = bc.courseid
WHERE
f.component = 'backup'
GROUP BY
c.id, c.fullname
ORDER BY
total_backup_space DESC;

calculates the total backup space used by each course by summing up the file sizes from the mdl_files table where the component is 'backup.' It provides a list of courses along with their corresponding total backup space, ordered in descending order.

Additionally, always back up your database before running any queries.