Recover Bacula Catalog Database After Accidental Purge of Jobs

I accidentally purged jobs from the Bacula catalog. I recovered by using the ASCII sql export from the previous day. I am using sqlite for the DB but mysql should work similar. Read through /etc/bacula/scripts/make_catalog_backup near the end for some breadcrumbs around database recovery.

I had the following jobs in the catalog before I screwed up.

1
| 10    | job_dracula    | 2012-09-09 11:57:11 | B    | F     | 288319   | 2234447730266 | T         |
2
| 13    | job_dracula    | 2012-09-14 23:58:05 | B    | F     | 288563   | 2260608426041 | T         |
3
| 45    | job_dracula    | 2012-10-14 00:56:53 | B    | F     | 0        | 0             | f         |
4
| 46    | job_dracula    | 2012-10-14 01:07:35 | B    | F     | 0        | 0             | R         |

For some reason I thought I will cleanup a bunch of failed jobs in the catalog using purge. Hindsight is 20 / 20 as they say….

1
This command can be DANGEROUS!!!
2

3
It purges (deletes) all Files from a Job,
4
JobId, Client or Volume; or it purges (deletes)
5
all Jobs from a Client or Volume without regard
6
to retention periods. Normally you should use the
7
PRUNE command, which respects retention periods.
8
You have the following choices:
9
     1: files
10
     2: jobs
11
     3: volume
12
Choose item to purge (1-3): 2
13
The defined Client resources are:
14
     1: bcla001-fd
15
     2: server002-fd
16
     3: dracula-fd
17
Select Client (File daemon) resource (1-3): 3
18
Begin purging jobs from Client "dracula-fd"
19
No Files found for client dracula-fd to purge from MyCatalog catalog.

Rebuild the DB as follow. Stopping bacula services first.

1
[ ok ] Stopping Bacula Director...: bacula-dir.
2
root@bcla001:/var/lib# /etc/init.d/bacula-fd stop
3
[ ok ] Stopping Bacula File daemon...: bacula-fd.
4
root@bcla001:/var/lib# /etc/init.d/bacula-sd stop
5
[ ok ] Stopping Bacula Storage daemon...: bacula-sd.
6

7
root@bcla001:/var/lib# ps -ef | grep bacula
8

9
root@bcla001:/var/lib/bacula# mv bacula.db bacula.db.101312
10

11
root@bcla001:/var/lib/bacula# sqlite3 bacula.db < bacula.sql
12

13
root@bcla001:/var/lib/bacula# chown bacula.bacula bacula.db
14

15
root@bcla001:/var/lib/bacula# ls -lh bacula.db
16
-rw-r--r-- 1 bacula bacula 1.1G Oct 14 00:43 bacula.db
17

18
root@bcla001:/var/lib/bacula# /etc/init.d/bacula-sd start
19
[ ok ] Starting Bacula Storage daemon...: bacula-sd.
20
root@bcla001:/var/lib/bacula# /etc/init.d/bacula-fd start
21
[ ok ] Starting Bacula File daemon...: bacula-fd.
22
root@bcla001:/var/lib/bacula# /etc/init.d/bacula-director start
23
[ ok ] Starting Bacula Director...: bacula-dir.
24

25
root@bcla001:/var/lib/bacula# ps -ef | grep bacula
26
bacula    8961     1  0 00:46 ?        00:00:00 /usr/sbin/bacula-sd -c /etc/bacula/bacula-sd.conf -u bacula -g tape
27
root      9006     1  0 00:46 ?        00:00:00 /usr/sbin/bacula-fd -c /etc/bacula/bacula-fd.conf
28
bacula    9036     1  0 00:46 ?        00:00:00 /usr/sbin/bacula-dir -c /etc/bacula/bacula-dir.conf -u bacula -g bacula