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.
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?
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?
Thanks,
--Greg
