I have a job called ARCHIVE-ALL that looks like this:
JobDefs {
Name = "ArchiveJob"
Type = Copy
Level = Full
Selection Type = SQLQuery
Messages = Standard
File Set = Copy
Pool = File
}
# Copy (to archive) all not-already-copied successful backup jobs
Job {
JobDefs = ArchiveJob
Name = "ARCHIVE-ALL"
Client = bstore
Selection Pattern = "select JobId from Job j where PoolId=2 and Type='B'and Jobstatus='T' and (select count(*) from Job where PriorJobId = j.JobId) = 0"
}
PoolId=2 corresponds to my "File" pool, which is where all the backups are written to initially. I also have an Archive pool where the copy jobs get written.
This worked well for over a year, but recently, the SQL query is matching a whole bunch of jobs that have already been copied, and it is failing to match some (like a Full backup I did on a client just today) that need to be copied.
To check if a job was already copied you have to verify priorjobid column in job table. The original job will have a priorjobid=0 and a copied job will have a priorjobid=<jobid> of the original. You can verify a required SQL in bacula source for PoolUncopiedJobs selection parameter.
In my humble opinion it is better to use PoolUncopiedJobs selection parameter for copy all successful jobs from the pool which was not already copied to the archive pool (nextpool parameter of the original pool or job).
I am theorizing that the reason it stopped working correctly is that a lot of the jobs have now been expired, and a recent round of full backups required that some jobs from the File pool had to be purged. Does this reset the PriorJobId to zero for any jobs that had a PriorJobId pointing to a job that is being purged?
Your SQL is very weird to me. You copy jobs from pool File to the Pool File? What is your pool File definition? What is a value of next pool parameter of the File pool?
If you have a job with jobid=1 in pool File (poolid=2) and you will copy this job to the pool Archive (which should have a different poolid) then when jobid=1 expire it will be deleted from the job table and you should not copy them again. If your copy jobs expire then they are deleted from the job table, so original jobs won't have a copy any more, so it is plausible that your sql will select original jobs to copy again.
Does anybody have an SQL query that will do what I want (which is to copy all the backup jobs from the File pool to the Archive pool that have not yet been copied?
Use a PoolUncopiedJobs selection parameter not a SQLQuery. It will do all what you want.
------------------------------------------------------------------------------
Monitor Your Dynamic Infrastructure at Any Scale With Datadog!
Get real-time metrics from all of your servers, apps and tools
in one place.
SourceForge users - Click here to start your Free Trial of Datadog now!
http://pubads.g.doubleclick.net/gampad/clk?id=241902991&iu=/4140
