Script to identify collection of tapes to take offsite?

[James Youngman]

My backup scheme is:

1. Data on my NAS is replicated in near-real-time to a hot standby
(not using Bareos). Both the NAS and hot standby carry
15-minute-granularity snapshots.
2. The NAS is backed up to tape with a standard (default, even)
full/incremental/differential scheme using 1 tape drive in a library.

That makes 3 copies in 2 formats, but there is still a missing piece:
physical location diversity (the 1 in 3-2-1).

I would like to regularly extract a set of tapes containing a recent
full backup (for each fileset on each client) to store offsite. I
understand that using a copy job would be one way to do this, but
because reading or writing a full backup is very slow and I only have
one tape drive, this isn't an attractive option. So my plan is to
select the media for a recent collection of Full backups and remove
those from the library. I suppose I will set volstatus=Archive on the
removed tapes so that Bareos doesn't try to re-cycle tapes that are
not physically present in the library.

I don't want to "manually" identify the set of tapes, because the
consequences of error are so huge (inability to fully recover
following a site loss event). But for my purposes, having slightly
out-of-date offsite backups is an acceptable trade-off.

Does anybody have a script which I can use to identify the media that
should be removed for offsite storage?

Thanks,
James.

[James Youngman]

I didn't get any replies, but I devised this query:

SELECT DISTINCT media.volumename, media.volstatus, pool.name AS pool,
media.mediatype, job.name AS job_name, job.starttime
FROM (
SELECT job.name as name, MAX(starttime) AS starttime
FROM job
WHERE job.level = 'F' AND level='F' AND jobstatus IN ('T', 'W')
AND job.name != 'BackupCatalog' AND AGE(starttime) < INTERVAL '60 days'
GROUP BY job.name
) AS successful_full_jobs
JOIN job ON job.name = successful_full_jobs.name AND job.starttime =
successful_full_jobs.starttime
JOIN jobmedia ON jobmedia.jobid = job.jobid
JOIN media ON media.mediaid = jobmedia.mediaid
JOIN pool ON pool.poolid = media.poolid
ORDER BY media.volumename;


I'm not that familiar with the data model, so this query may be incorrect.

One thing I find unsatisfactory about it is the "AGE(starttime) <
INTERVAL '60 days'" condition. If I leave that out, I get results
from full backups taken a long time ago with job names which are no
longer in use (i.e. which have been replaced by other jobs also in the
results of the query). But I can't see a better way to eliminate
those.

Can anybody suggest an improvement or correction?

Thanks,
James.

[Jon Schewe]

I can't speak to your query, however here's how I solve the offsite issue. I have a separate pool of tapes for offsite backups. I run a full backup to that pool every 2 weeks (the interval that I rotate tapes offsite). 

In the defaults for my offsite jobs I have

  RunScript {
    RunsOnClient = no
    RunsWhen = after
    FailJobOnError = yes
    Command  = "/etc/bareos/record-offsite-volume %j %v"
  }

--- record-offsite-volume ---

#!/bin/sh

jobid=$1
volume=$2

printf "${volume}\n" > /etc/bareos/offsite-status/offsite-volume-${jobid} || mail -s "offsite info lost ${volume}" root@localhost
exit 0

-----

My offsite backup catalog runs last due to priority and has

  RunScript {
    RunsOnClient = no
    RunsWhen = After
    FailJobOnError = yes
    Command  = "/etc/bareos/after-offsite %j %v"
  }
 RunScript {
    RunsOnClient = no
    RunsWhen = After
    FailJobOnError = no
    Command = "/etc/bareos/release-drive"
  }

--- release-drive ---

#!/bin/sh

debug() { ! "${log_debug-false}" || log "DEBUG: $*" >&2; }
log() { printf '%s\n' "$*"; }
warn() { log "WARNING: $*" >&2; }
error() { log "ERROR: $*" >&2; }
fatal() { error "$*"; exit 1; }
try() { "$@" || fatal "'$@' failed"; }

mydir=$(cd "$(dirname "$0")" && pwd -L) || fatal "Unable to determine script directory"

cleanup() {
    debug "In cleanup"
}
trap 'cleanup' INT TERM EXIT

echo "release storage=PV-124T" | bconsole

-------

--- after-offsite ---

#!/usr/bin/env python

import warnings
with warnings.catch_warnings():
    import re
    import sys
    from optparse import OptionParser
    import subprocess
    import datetime
    import os
    import os.path
    import smtplib
    from email.mime.text import MIMEText

status_dir="/etc/bareos/offsite-status"

def cleanup_files():
    # clean up the files that contain the volume information
    for name in os.listdir(status_dir):
        if name.startswith("offsite-volume-"):
            os.remove(os.path.join(status_dir, name))

def gather_used_volumes():
    volumes={}
    for name in os.listdir(status_dir):
        if name.startswith("offsite-volume-"):
            line = open(os.path.join(status_dir, name)).read().rstrip()
            for volume in line.split("|"):
                volumes[volume] = 1
    return volumes.keys()

def send_message(recipient, subject, message):
    msg = MIMEText(message)
    msg['Subject'] = subject
    msg['From'] = 'root'
    msg['To'] = recipient

    server = smtplib.SMTP('localhost')
    server.sendmail('root', [recipient], msg.as_string())
    server.quit()

def compose_and_send_message(volumes_used):
    message="""
Eject offsite tapes and send to offsite storage
Volumes Used:
%s""" % ("\n".join(volumes_used))
    send_message('root', 'Offsite Tape Rotation', message)

def mark_volume_used(volume):
    subprocess.call("echo 'update volume=%s volstatus=Used' | /usr/bin/bconsole" % (volume), shell=True)

def main(argv=None):
    if argv is None:
        argv = sys.argv

    parser = OptionParser()
    (options, args) = parser.parse_args(argv)

    jobid=args[1]
    volume=args[2]

    # record offsite volume first and then send the email about what to do
    subprocess.call("/etc/bareos/record-offsite-volume %s %s" % (jobid, volume), shell=True)

    volumes_used = gather_used_volumes()

    # mark all volumes as used
    for volume in volumes_used:
        mark_volume_used(volume)

    compose_and_send_message(volumes_used)
    cleanup_files()



if __name__ == "__main__":
    sys.exit(main())

--------