On 12/09/2011 10:26 AM, Rodrigo Renie Braga wrote:
> Hello everyone.
>
> In my first attempt using Copy Jobs, I was creating one Copy Job for each of my Clients, with the following SQL Selection Pattern:
>
>         SELECT max(JobId)
>              FROM Job
>             WHERE Name = 'someClientJobName'
>               AND Type = 'B'
>               AND Level = 'F'
>               AND JobStatus in ('T', 'W')
>               AND JobBytes > 0;
>
> Basically, every Client had 2 Jobs: one for a normal daily Backup and one for copying it's last Full Backup, using the above SQL Query.
>
> My question is: do I really need to create a different Copy Job for every Client or can I use only ONE Copy Job that Selects the
> latest Full Backup JobID of all my clients?

I have my full backups go to their own pool.
Then I have just one copy job defined like so:

Job {
 Name = "OffsiteBackup"
 JobDefs = "DefaultJob"
 Type = Copy
 Level = Full
 # Uses the 'Next Pool' definition from FullPool for where to write the copies to.
 Pool = FullPool
 # Use SQL to select the most recent (successful) Full backup for each job written to the FullPool pool.
 Selection Type = SQLQuery
 Selection Pattern = "SELECT MAX(Job.JobId) FROM Job, Pool WHERE Job.Level = 'F' and Job.Type = 'B' and Job.JobStatus = 'T' and
Pool.Name = 'FullPool' and Job.PoolId = Pool.PoolId GROUP BY Job.Name ORDER BY MAX(Job.JobId);"
 Allow Duplicate Jobs = yes
 Allow Higher Duplicates = no
}

So that SQL formatted over multiple lines for easier reading is:

SELECT
       MAX(Job.JobId)
FROM
       Job, Pool
WHERE
       Job.Level = 'F'
       and Job.Type = 'B'
       and Job.JobStatus = 'T'
       and Pool.Name = 'FullPool'
       and Job.PoolId = Pool.PoolId
GROUP BY
       Job.Name
ORDER BY
       MAX(Job.JobId);

Regards,

----------
Jim Barber
DDI Health