On Tue, Aug 11, 2009 at 02:39:39PM -0400, John Lockard wrote:
> I have modified my query.sql to include some queries that
> I use frequently and I thought maybe someone else would
> find them useful additions. Also, I was wondering if anyone
> had queries which they find useful and would like to share.
>
> In my setup, I need to rotate tapes on a weekly basis to
> keep offsites in case of emergency, so I need to find certain
> groups of tapes for easy removal and it's easier to group
> them in query output than having to scan down a 57 item long
> list and pick out the ones I need (and other similar needs).
>
> I hope someone finds this useful,
> -John
<SNIP>
I've just done another one which might be useful...
I keep backups on disk for about a week over a month, then
I migrate my Differential and Full backups to tape for safe
keeping. Bacula doesn't purge media when all jobs from a
virtual tape have been migrated to physical tape, so the
disk jobs stay around for the full life of the job. In my
case that means my Full backups would live on disk for
9 months, even though they were migrated to tape almost 8
months ago.
So, I need a way to find all MediaId's which have had *ALL*
of their jobs migrated to tape so that I can purge them.
Here's what I've come up with. (If there's an easier way
to do this, please tell me). (Works on MySQL)
:Test List Migrated Jobs stored on Media
*Order by (Job or Media):
!DROP TABLE tempmig;
!DROP TABLE tempmig2;
!DROP TABLE tempmig3;
CREATE TABLE tempmig (MediaId INT, Type BINARY(1));
CREATE TABLE tempmig2 (MediaId INT, Type BINARY(1));
CREATE TABLE tempmig3 (MediaId INT NOT NULL);
INSERT INTO tempmig
SELECT JobMedia.MediaId,Job.Type
FROM Job,JobMedia,Media
WHERE JobMedia.MediaId=Media.MediaId
AND Job.JobId=JobMedia.JobId
AND Job.Type='M';
INSERT INTO tempmig2
SELECT JobMedia.MediaId,Job.Type
FROM Job,JobMedia,Media
WHERE tempmig.MediaId=JobMedia.MediaId
AND Job.JobId=JobMedia.JobId
AND Job.Type!='M';
INSERT INTO tempmig3
SELECT tempmig.MediaId
FROM tempmig
LEFT JOIN tempmig2
ON tempmig2.MediaId = tempmig.MediaId
WHERE tempmig2.MediaId IS NULL;
SELECT DISTINCT
Job.JobId,JobMedia.MediaId,Job.Name,Job.Type,Job.Level,Job.JobStatus AS
Status,Job.JobFiles AS Files,Job.JobBytes/(1024*1024*1024) AS GB
FROM JobMedia,Job,tempmig3
WHERE JobMedia.JobId=Job.JobId
AND JobMedia.MediaId=tempmig3.MediaId
ORDER by JobMedia.%1Id ASC;
!DROP TABLE tempmig;
!DROP TABLE tempmig2;
!DROP TABLE tempmig3;
-John
--
"No matter how sophisticated you may be, a large granite mountain
cannot be denied - it speaks in silence to the very core of your
being" - Ansel Adams
-------------------------------------------------------------------
John M. Lockard | U of Michigan - School of Information
Unix and Security Admin | 1214 SI North - 1075 Beal Ave.
jlock...@umich.edu | Ann Arbor, MI 48109-2112
www.umich.edu/~jlockard | 734-615-8776 | 734-647-8045 FAX
-------------------------------------------------------------------
------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
