What jobs are on these tapes?

I use Bacula. To be clear, I wrote part of Bacula (the PostgreSQL part). Today I need to find out what jobs are on each of three tapes. Why? I just happened to cancel a 1.5TB job a few hours before it was to finish. By this time it had written to three tapes. I am guessing that the last two tapes can be reused. I’m also guessing that the first one cannot be.

I will eventually purge this job from the Catalog database, but first, I’ll use the database to locate the information I need.

The job in question is 66. Here are the tapes it wrote to:

mysql> select distinct(MediaId) from JobMedia where JobId = 66;
+---------+
| MediaId |
+---------+
|      26 |
|      27 |
|      28 |
+---------+
3 rows in set (0.03 sec)

mysql>

Yes, I’m using MySQL here. It was not my choice.

What labels are these?

mysql> select MediaId, VolumeName from Media where MediaId in (select distinct(MediaId) from JobMedia where JobId = 66);
+---------+------------+
| MediaId | VolumeName |
+---------+------------+
|      26 | PEQ955L4   |
|      27 | PEQ956L4   |
|      28 | PEQ957L4   |
+---------+------------+
3 rows in set (0.00 sec)

mysql>

OK. That matches exactly what the job tells me:

  Volume name(s):         PEQ955L4|PEQ956L4|PEQ957L4

What are all the jobs on these tapes?

mysql> select distinct JobId from JobMedia where MediaId in
    -> (select distinct(MediaId) from JobMedia where JobId = 66);
+-------+
| JobId |
+-------+
|    65 |
|    66 |
+-------+
2 rows in set (0.08 sec)

mysql>

We have just two jobs. What are their names?

mysql> SELECT JobId, Job, Name FROM Job WHERE JobId IN
    ->    (select distinct JobId from JobMedia where MediaId in
    ->       (select distinct(MediaId) from JobMedia where JobId = 66));
+-------+------------------------------------------+-------------------+
| JobId | Job                                      | Name              |
+-------+------------------------------------------+-------------------+
|    65 | StuffBackd2008.07.2009-08-24_08.27.47_04 | StuffBackd2008.07 |
|    66 | StuffBackd2008.08.2009-08-24_08.27.50_05 | StuffBackd2008.08 |
+-------+------------------------------------------+-------------------+
2 rows in set (0.07 sec)

mysql>

Now, what tapes does Job 65 use?

mysql> select MediaId, VolumeName from Media where MediaId in (select distinct(MediaId) from JobMedia where JobId = 65);
+---------+------------+
| MediaId | VolumeName |
+---------+------------+
|      24 | PEQ953L4   |
|      25 | PEQ954L4   |
|      26 | PEQ955L4   |
+---------+------------+
3 rows in set (0.00 sec)

mysql>

Look at the Volumes for Job 65 and Job 66, I can see that I must keep Volume PEQ955L4. But the other two Volumes used for Job 66 can be wiped. Let me see what Jobs are used by those two Volumes

mysql> select distinct(JobId) from JobMedia where MediaId IN (27, 28);
+-------+
| JobId |
+-------+
|    66 |
+-------+
1 row in set (0.00 sec)

mysql>

Good. Just the canceled Job: 66.

Before I did anything, I backed up my Catalog database first.

Yes, good. Now I can purge the records for that one Job.

*purge files jobid=66

This command is can be DANGEROUS!!!

It purges (deletes) all Files from a Job,
JobId, Client or Volume; or it purges (deletes)
all Jobs from a Client or Volume without regard
for retention periods. Normally you should use the
PRUNE command, which respects retention periods.
Automatically selected Catalog: MyCatalog
Using Catalog "MyCatalog"

Now I can wipe things out for the two Volumes:

*purge volume=PEQ956L4

This command is can be DANGEROUS!!!

It purges (deletes) all Files from a Job,
JobId, Client or Volume; or it purges (deletes)
all Jobs from a Client or Volume without regard
for retention periods. Normally you should use the
PRUNE command, which respects retention periods.
1 File on Volume "AAM266L4" purged from catalog.
There are no more Jobs associated with Volume "PEQ956L4". Marking it purged.

*purge volume=PEQ957L4

This command is can be DANGEROUS!!!

It purges (deletes) all Files from a Job,
JobId, Client or Volume; or it purges (deletes)
all Jobs from a Client or Volume without regard
for retention periods. Normally you should use the
PRUNE command, which respects retention periods.
0 Files on Volume "PEQ957L4" purged from catalog.
There are no more Jobs associated with Volume "PEQ957L4". Marking it purged.

*

The key point with the purge is that it removes Jobs (associated with that Volume) from the Catalog. It does not remove data from the Volume. This process also sets the Volume status to Purged.

From the Bacula documentation:

Automatic recycling of Volumes is performed by Bacula only when it wants a new Volume and no appendable Volumes are available in the Pool. It will then search the Pool for any Volumes with the Recycle flag set and the Volume Status is Purged. At that point, it will choose the oldest purged volume and recycle it.

Thus, these two Purged Volumes will be used after all the Volumes with status Append have been used.

Hope this helps.