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.

root@bcla001:~# echo "list jobs" | bconsole | grep -i dracula
| 10    | job_dracula    | 2012-09-09 11:57:11 | B    | F     | 288319   | 2234447730266 | T         |
| 13    | job_dracula    | 2012-09-14 23:58:05 | B    | F     | 288563   | 2260608426041 | T         |
| 45    | job_dracula    | 2012-10-14 00:56:53 | B    | F     | 0        | 0             | f         |
| 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....

*purge 

This command 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
to retention periods. Normally you should use the
PRUNE command, which respects retention periods.
You have the following choices:
     1: files
     2: jobs
     3: volume
Choose item to purge (1-3): 2
The defined Client resources are:
     1: bcla001-fd
     2: server002-fd
     3: dracula-fd
Select Client (File daemon) resource (1-3): 3
Begin purging jobs from Client "dracula-fd"
No Files found for client dracula-fd to purge from MyCatalog catalog.

Rebuild the DB as follow. Stopping bacula services first.

root@bcla001:/var/lib# /etc/init.d/bacula-director stop
[ ok ] Stopping Bacula Director...: bacula-dir.
root@bcla001:/var/lib# /etc/init.d/bacula-fd stop
[ ok ] Stopping Bacula File daemon...: bacula-fd.
root@bcla001:/var/lib# /etc/init.d/bacula-sd stop
[ ok ] Stopping Bacula Storage daemon...: bacula-sd.

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

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

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

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

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

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

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