Backup Central

Hello there,

I'm currently trying to build a backup procedure that will backup my servers to an external disk, then copy the backups to my iMac, so that I have another copy of the data, if my main server were to die, along with the external backup HDD. I only want to keep the copied volumes for a week - they are not for archival purposes - just so that I have a another copy on my network of the most recent data, intended to be used in a DR situation.


So far, I have setup successful backups to the external backup HDD for my servers, which have been running for some time now, with no issues. However the copy part of the process has been causing some grief...


Here's an example of my problem:


I have a few full backups of my SVN repositories in the following volumes:
*list media pool=SVN_Full

+---------+---------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+

| MediaId | VolumeName | VolStatus | Enabled | VolBytes | VolFiles | VolRetention | Recycle | Slot | InChanger | MediaType | LastWritten |

+---------+---------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+

| 6 | SVN_Full_0006 | Full | 1 | 1,073,731,363 | 0 | 2,419,200 | 1 | 0 | 0 | File | 2011-12-26 00:39:18 |

| 240 | SVN_Full_0240 | Full | 1 | 1,073,716,492 | 0 | 2,419,200 | 1 | 0 | 0 | File | 2012-01-02 00:44:06 |

| 248 | SVN_Full_0248 | Full | 1 | 1,073,684,091 | 0 | 2,419,200 | 1 | 0 | 0 | File | 2012-01-09 00:41:43 |

| 254 | SVN_Full_0254 | Append | 1 | 70,929,259 | 0 | 2,419,200 | 1 | 0 | 0 | File | 2012-01-09 00:41:50 |

+---------+---------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+



I have a copy job as follows:


Job {

Name = "SVN Repositories Full Copy"

JobDefs = DefaultCopy

Type = Copy

Client = FileServer1-fd

FileSet = "SVN Repositories"

Pool = SVN_Full

Messages = Standard

Selection Type = SQLQuery

Selection Pattern = "SELECT DISTINCT Job.JobId,Job.StartTime FROM Job,Pool

WHERE Pool.Name = 'SVN_Full'

AND Pool.PoolId = Job.PoolId

AND Job.Type = 'B'

AND Job.JobStatus = 'T'

AND Job.JobId

NOT IN (SELECT PriorJobId FROM Job WHERE TYPE = 'C' AND Job.JobStatus = 'T' AND PriorJobId != 0) ORDER BY Job.StartTime;"

Schedule = "SVN Weekly Schedule"

Enabled = No

}



Then I run the copy job manually to copy all my SVN Full backups to my iMac:*run job="SVN Repositories Full Copy" yes > http://pastebin.com/Rhdsf1sJ


So, you can see I get:
14-Jan 22:41 FileServer1-dir JobId 3340: The following 4 JobIds were chosen to be copied: 2962,3013,3076,3148


"The following jobs will be copied: 2962,3013,3076,3148 - Ok, we have selected four full backups in the SVN_Full pool which have not been copied before, which will be copied in this job."

"All appears to be well"...






However [here's the problem], I then want to check to see what happens next time this copy job runs, so to simulate things I ran the selection query against my DB (I am expecting to see no jobs be returned for the query, as I have already copied the jobs to my iMac):


SELECT DISTINCT Job.JobId,Job.StartTime FROM Job,Pool



WHERE Pool.Name = 'SVN_Full'



AND Pool.PoolId = Job.PoolId



AND Job.Type = 'B'



AND Job.JobStatus = 'T'



AND Job.JobId NOT IN (SELECT PriorJobId FROM Job WHERE TYPE = 'C' AND Job.JobStatus = 'T' AND PriorJobId != 0) ORDER BY Job.StartTime;





But the following is returned from the DB:
JobId StartTime
2962 2011-12-12 00:42:17
3013 2011-12-19 00:35:55
3076 2011-12-26 00:38:17





These jobs were copied before, were they not??

"Ok, let's give it a go anyway":
*run job="SVN Repositories Full Copy" yes > http://pastebin.com/tXD7nd7J





So, we get jobs: 2962,3013,3076 copied again...


If I then run the same SQL query as above again, I get the first job (which was already copied above) returned:



JobId StartTime
3148 2012-01-02 00:42:18






If I run: "run job="SVN Repositories Full Copy" yes" again, (as the query result showed us) it copies job 3148 again... > http://pastebin.com/fY6bSAmL

If I then run the same SQL query as above again, I get the other three jobs returned again:
JobId StartTime

2962 2011-12-12 00:42:17

3013 2011-12-19 00:35:55

3076 2011-12-26 00:38:17


Then we go round an round in circles...


Here's my other configs that may be of use:


JobDefs {
Name = DefaultCopy
Type = Copy
Level = Full
Client = FileServer1-fd
Messages = On_Error
Selection Type = Job
}



Storage {
Name = SVN_Full_Copy
Password = <snip>
Address = FileServer1
SDPort = 9103
Device = SVN_Full_Copy
Media Type = File
Maximum Concurrent Jobs = 20
}



Pool {
Name = "SVN_Full_Copy"
Pool Type = *Copy
Volume Retention = 1 weeks
Recycle = yes
AutoPrune = yes
LabelFormat = SVN_Full_Copy_
Maximum Volume Bytes = 1G
Storage = "SVN_Full_Copy"
}



Does anyone know why my copy routine is going round in circles?? I sure the problem will be a mistake on my part, but I can't for the life of me pinpoint why it's happening! If any further info is needed, please let me know.


Please help if you can!


Kind regards,


Joe Nyland

Why not just write a shell script to copy
the archive volumes and either run it as a
type=Admin job or from cron? That's
what I do. Have it set up so each
client has it's own pool of "use-once"
volumes with the date-time in the name.
Simple 'touch' and 'find -newer' commands
figure out what to copy when.


------------------------------------------------------------------------------
RSA(R) Conference 2012
Mar 27 - Feb 2
Save $400 by Jan. 27
Register now!
http://p.sf.net/sfu/rsa-sfdev2dev2
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Hi there,


Thanks for replying.


I had thought of doing it with a bash script like this, yes, but it seems (According to http://www.bacula.org/manuals/en/concepts/concepts/Migration_Copy.html) that Bacula is capable of doing what I want. It is preferred to use Bacula for this task, so that backups are centrally managed within Bacula and volumes are cleaned and recycled automatically as per my pool settings.


I feel like I'm nearly there - i.e. I am able to copy the data to the other system - volumes are being created. However I think the only indication to the cause of my problems is the following lines which are taken from my output from the copy job, which I posted in my original email:
14-Jan 23:13 FileServer1-sd JobId 3348: User defined maximum volume capacity 1,073,741,824 exceeded on device "SVN_Full_Copy" (/mnt/mac_backup/Bacula/SVN/Full).
14-Jan 23:13 FileServer1-sd JobId 3348: End of medium on Volume "SVN_Full_Copy_0281" Bytes=1,073,729,232 Blocks=16,646 at 14-Jan-2012 23:13.
14-Jan 23:13 FileServer1-dir JobId 3348: There are no more Jobs associated with Volume "SVN_Full_Copy_0280". Marking it purged.
14-Jan 23:13 FileServer1-dir JobId 3348: All records pruned from Volume "SVN_Full_Copy_0280"; marking it "Purged"
14-Jan 23:13 FileServer1-dir JobId 3348: Recycled volume "SVN_Full_Copy_0280"
14-Jan 23:13 FileServer1-sd JobId 3348: Recycled volume "SVN_Full_Copy_0280" on device "SVN_Full_Copy" (/mnt/mac_backup/Bacula/SVN/Full), all previous data lost.
14-Jan 23:13 FileServer1-sd JobId 3348: New volume "SVN_Full_Copy_0280" mounted on device "SVN_Full_Copy" (/mnt/mac_backup/Bacula/SVN/Full) at 14-Jan-2012 23:13.


The way I read the above output is that [possibly] my volumes on my iMac (which were created on the first run of the copy job) are 'expiring'(?) and thus being over written. So the next time the copy job runs, Bacula realises that it doesn't have a copy of the job IDs which were purged from the copied volumes, and thus queues them again for copy.


Any ideas why this would be happening? As, (as far as I am aware from my settings) the volume should not being expiring (yet)... Note: I could be wrong though, and I could have misread the job logs.


Many thanks for you help.


Kind regards,


Joe Nyland


On 15 Jan 2012, at 00:55, starlight < at > binnacle.cx ([email]starlight < at > binnacle.cx[/email]) wrote:
Why not just write a shell script to copy
the archive volumes and either run it as a
type=Admin job or from cron? That's
what I do. Have it set up so each
client has it's own pool of "use-once"
volumes with the date-time in the name.
Simple 'touch' and 'find -newer' commands
figure out what to copy when.

On Sat, Jan 14, 2012 at 11:46:21PM +0000, Joe Nyland wrote:
I have a copy job as follows:
Selection Type = SQLQuery
Selection Pattern = "SELECT DISTINCT Job.JobId,Job.StartTime FROM Job,Pool
WHERE Pool.Name = 'SVN_Full'
AND Pool.PoolId = Job.PoolId
AND Job.Type = 'B'
AND Job.JobStatus = 'T'
AND Job.JobId
NOT IN (SELECT PriorJobId FROM Job WHERE TYPE = 'C' AND Job.JobStatus = 'T' AND PriorJobId != 0) ORDER BY Job.StartTime;"

I do something similar and it works for me. However, in my special case
I do additional messin with the pools. Actually I backup to disk, then
copy it to one tape TAPE and one tape ARCH, this is only for the 'ARCH'
job. The 'ARCH' is actually just an offsite version and in no way an
archive, only backups within the last 10 days are considered, otherwise
you start and copy old expired purged jobs again ater you deleted them
from the db.
-> INTERVAL... should be less than your retention time if you delete
purged jobs now and then
As my statement looks pretty much the same, I'd check the 'NOT IN'-part,
especially the 'JobStatus'.

Selection Pattern = "select a.JobId from Job AS a JOIN Pool AS b
ON a.PoolId=b.PoolId AND b.Name!='ARCH'
AND b.Name!='TAPE'
WHERE a.Type='B' AND a.JobID NOT IN
(SELECT PriorJobId FROM Job AS c JOIN Pool AS d
ON c.PoolId=d.PoolId AND d.Name='ARCH')
AND a.EndTime>(now()-INTERVAL '10 DAY') ORDER BY a.StartTime ASC;"

Postgres syntax here, but I *think* I had the very same statement a few
months ago when I started with MySQL.

Regards,
Adrian
--
LiHAS - Adrian Reyer - Hessenwiesenstraße 10 - D-70565 Stuttgart
Fon: +49 (7 11) 78 28 50 90 - Fax: +49 (7 11) 78 28 50 91
Mail: lihas < at > lihas.de - Web: http://lihas.de
Linux, Netzwerke, Consulting & Support - USt-ID: DE 227 816 626 Stuttgart

------------------------------------------------------------------------------
RSA(R) Conference 2012
Mar 27 - Feb 2
Save $400 by Jan. 27
Register now!
http://p.sf.net/sfu/rsa-sfdev2dev2
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Hi,

Thanks for you input.


Unfortunately, I am no SQL expert, and I get the following when trying to run your selection query against my Bacula MySQL DB:


select a.JobId from Job AS a JOIN Pool AS b
ON a.PoolId=b.PoolId AND b.Name!='ARCH'
AND b.Name!='TAPE'
WHERE a.Type='B' AND a.JobID NOT IN
(SELECT PriorJobId FROM Job AS c JOIN Pool AS d
ON c.PoolId=d.PoolId AND d.Name='ARCH')
AND a.EndTime>(now()-INTERVAL '10 DAY') ORDER BY a.StartTime ASC;


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY a.StartTime' at line 7



Any ideas?


Thanks for your help.


Joe



On 15 Jan 2012, at 17:28, Adrian Reyer wrote:
On Sat, Jan 14, 2012 at 11:46:21PM +0000, Joe Nyland wrote:
I have a copy job as follows:
Selection Type = SQLQuery
Selection Pattern = "SELECT DISTINCT Job.JobId,Job.StartTime FROM Job,Pool
WHERE Pool.Name = 'SVN_Full'
AND Pool.PoolId = Job.PoolId
AND Job.Type = 'B'
AND Job.JobStatus = 'T'
AND Job.JobId
NOT IN (SELECT PriorJobId FROM Job WHERE TYPE = 'C' AND Job.JobStatus = 'T' AND PriorJobId != 0) ORDER BY Job.StartTime;"

I do something similar and it works for me. However, in my special case
I do additional messin with the pools. Actually I backup to disk, then
copy it to one tape TAPE and one tape ARCH, this is only for the 'ARCH'
job. The 'ARCH' is actually just an offsite version and in no way an
archive, only backups within the last 10 days are considered, otherwise
you start and copy old expired purged jobs again ater you deleted them
from the db.
-> INTERVAL... should be less than your retention time if you delete
purged jobs now and then
As my statement looks pretty much the same, I'd check the 'NOT IN'-part,
especially the 'JobStatus'.

Selection Pattern = "select a.JobId from Job AS a JOIN Pool AS b
ON a.PoolId=b.PoolId AND b.Name!='ARCH'
AND b.Name!='TAPE'
WHERE a.Type='B' AND a.JobID NOT IN
(SELECT PriorJobId FROM Job AS c JOIN Pool AS d
ON c.PoolId=d.PoolId AND d.Name='ARCH')
AND a.EndTime>(now()-INTERVAL '10 DAY') ORDER BY a.StartTime ASC;"

Postgres syntax here, but I *think* I had the very same statement a few
months ago when I started with MySQL.

Regards,
Adrian
--
LiHAS - Adrian Reyer - Hessenwiesenstraße 10 - D-70565 Stuttgart
Fon: +49 (7 11) 78 28 50 90 - Fax: +49 (7 11) 78 28 50 91
Mail: lihas < at > lihas.de ([email]lihas < at > lihas.de[/email]) - Web: http://lihas.de
Linux, Netzwerke, Consulting & Support - USt-ID: DE 227 816 626 Stuttgart

On 01/15/2012 04:17 PM, Joe Nyland wrote:
select a.JobId from Job AS a JOIN Pool AS b
ON a.PoolId=b.PoolId AND b.Name!='ARCH'
AND b.Name!='TAPE'
WHERE a.Type='B' AND a.JobID NOT IN
(SELECT PriorJobId FROM Job AS c JOIN Pool AS d
ON c.PoolId=d.PoolId AND d.Name='ARCH')
AND a.EndTime>(now()-INTERVAL '10 DAY') ORDER BY a.StartTime ASC;

Drop the quotes around 10 DAY.


--
Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
alaric < at > caerllewys.net alaric < at > metrocast.net phil < at > co.ordinate.org
Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
It's not the years, it's the mileage.

------------------------------------------------------------------------------
RSA(R) Conference 2012
Mar 27 - Feb 2
Save $400 by Jan. 27
Register now!
http://p.sf.net/sfu/rsa-sfdev2dev2
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Hi,

Thanks for your pointer Phil - it seems to be valid now.

However, I am still unable to see what this query is doing . If I run it against my catalogue DB, I just get a long list of JobIDs, which don't look right for my SVN_Full pool. I have a feeling I may be over complicating thigs using a SQL query?

Re-reading the documentation on Copy and Migration, there's a job option, which I had missed last time I read the page:
PoolUncopiedJobsThis selection which copies all jobs from a pool to an other pool which were not copied before is available only for copy Jobs.
What are the pro's/con's with this option, over a SQL query, such as the one kindly suggested by Adrian Reyer a few days ago?
Thanks,
Joe




On 15 Jan, 2012,at 10:47 PM, Phil Stracchino <alaric < at > metrocast.net> wrote:

On 01/15/2012 04:17 PM, Joe Nyland wrote:
select a.JobId from Job AS a JOIN Pool AS b
ON a.PoolId=b.PoolId AND b.Name!='ARCH'
AND b.Name!='TAPE'
WHERE a.Type='B' AND a.JobID NOT IN
(SELECT PriorJobId FROM Job AS c JOIN Pool AS d
ON c.PoolId=d.PoolId AND d.Name='ARCH')
AND a.EndTime>(now()-INTERVAL '10 DAY') ORDER BY a.StartTime ASC;

Drop the quotes around 10 DAY.


--
Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
alaric < at > caerllewys.net ([email]alaric < at > caerllewys.net[/email]) alaric < at > metrocast.net ([email]alaric < at > metrocast.net[/email]) phil < at > co.ordinate.org ([email]phil < at > co.ordinate.org[/email])
Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
It's not the years, it's the mileage.

------------------------------------------------------------------------------
RSA(R) Conference 2012
Mar 27 - Feb 2
Save $400 by Jan. 27
Register now!
http://p.sf.net/sfu/rsa-sfdev2dev2
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net ([email]Bacula-users < at > lists.sourceforge.net[/email])
https://lists.sourceforge.net/lists/listinfo/bacula-users

On Mon, Jan 16, 2012 at 01:23:37PM +0000, Joe Nyland wrote:
However, I am still unable to see what this query is doing . If I run it against my catalogue DB, I just get a long list of JobIDs, which don't look right for my SVN_Full pool. I have a feeling I may be over complicating thigs using a SQL query?

Well, this special SQL selects all jobs of any pool that is not the
'ARCH' pool. In your case, as you only wish to copy a specific pool, it
would be, still with 'ARCH' as the target pool:

select a.JobId from Job AS a JOIN Pool AS b
ON a.PoolId=b.PoolId AND b.Name='SVN_Full' AND b.Name!='TAPE'
WHERE a.Type='B' AND a.JobID NOT IN
(SELECT PriorJobId FROM Job AS c JOIN Pool AS d
ON c.PoolId=d.PoolId AND d.Name='ARCH')
AND a.EndTime>(now()-INTERVAL '10 DAY') ORDER BY a.StartTime ASC;

PoolUncopiedJobs
This selection which copies all jobs from a pool to an other pool
which were not copied before is available only for copy Jobs.
What are the pro's/con's with this option, over a SQL query, such as the one kindly suggested by Adrian Reyer a few days ago?

I have not used this option because of my special setup as I have to
copy each original job twice. This is only possible with SQL. For you it
might just work.

Regards,
Adrian
--
LiHAS - Adrian Reyer - Hessenwiesenstraße 10 - D-70565 Stuttgart
Fon: +49 (7 11) 78 28 50 90 - Fax: +49 (7 11) 78 28 50 91
Mail: lihas < at > lihas.de - Web: http://lihas.de
Linux, Netzwerke, Consulting & Support - USt-ID: DE 227 816 626 Stuttgart

------------------------------------------------------------------------------
RSA(R) Conference 2012
Mar 27 - Feb 2
Save $400 by Jan. 27
Register now!
http://p.sf.net/sfu/rsa-sfdev2dev2
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Thanks for the explanation Andrian.

In the meantime, I've given the 'PoolUncopiedJobs' option a go, and it appears to work just as well as the SQL query that I originally included in my email. However, Im still stuck in the loop mentioned earlier - I still appear to be copying two jobs, then when the copy job is run again, I get two other jobs copied, which seem to overwrite the previous two jobs in my 'copied' pool. Thus the next time the copy job is run the first two jobs are run again. And so on...

Can anyone offer any pointers why I might be seeing this behaviour. The volume retention is set to 1 week on my 'copied' pool. As I understand it, jobs which have been written to the copied pool should not be overwritten, until 1 week has passed. Am I wrong?

Thank you.

Joe


Sent from my iPhone

On 16 Jan 2012, at 18:01, Adrian Reyer <bacula-lists < at > lihas.de> wrote:

On Mon, Jan 16, 2012 at 01:23:37PM +0000, Joe Nyland wrote:
However, I am still unable to see what this query is doing . If I run it against my catalogue DB, I just get a long list of JobIDs, which don't look right for my SVN_Full pool. I have a feeling I may be over complicating thigs using a SQL query?

Well, this special SQL selects all jobs of any pool that is not the
'ARCH' pool. In your case, as you only wish to copy a specific pool, it
would be, still with 'ARCH' as the target pool:

select a.JobId from Job AS a JOIN Pool AS b
ON a.PoolId=b.PoolId AND b.Name='SVN_Full' AND b.Name!='TAPE'
WHERE a.Type='B' AND a.JobID NOT IN
(SELECT PriorJobId FROM Job AS c JOIN Pool AS d
ON c.PoolId=d.PoolId AND d.Name='ARCH')
AND a.EndTime>(now()-INTERVAL '10 DAY') ORDER BY a.StartTime ASC;

PoolUncopiedJobs
This selection which copies all jobs from a pool to an other pool
which were not copied before is available only for copy Jobs.
What are the pro's/con's with this option, over a SQL query, such as the one kindly suggested by Adrian Reyer a few days ago?

I have not used this option because of my special setup as I have to
copy each original job twice. This is only possible with SQL. For you it
might just work.

Regards,
Adrian
--
LiHAS - Adrian Reyer - Hessenwiesenstraße 10 - D-70565 Stuttgart
Fon: +49 (7 11) 78 28 50 90 - Fax: +49 (7 11) 78 28 50 91
Mail: lihas < at > lihas.de - Web: http://lihas.de
Linux, Netzwerke, Consulting & Support - USt-ID: DE 227 816 626 Stuttgart

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Bacula-users mailing list
Bacula-users < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users