* # cat /usr/lib64/bacula/query.sql*
#
# This file contains sample queries that you can possibly use in
# your query.sql file. However, please note that these examples
# are not supported. They may or may not work -- in fact, they may
# work with one SQL engine and not another. As a consequence,
# please do not submit bug reports on either query.sql or
# sample-query.sql
#
# If you find that they work for MySQL and not for PostgreSQL,
# then please send us a whole new file corrected for PostgreSQL
# and we will post it in the examples directory for everyone
# to use.
#
# 1
:List up to 20 places where a File is saved regardless of the directory
*Enter Filename (no path):
SELECT DISTINCT Job.JobId as JobId, Client.Name as Client,
Path.Path,Filename.Name,StartTime,Level,JobFiles,JobBytes
FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId
AND JobStatus IN ('T','W') AND Job.JobId=File.JobId
AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId
AND Filename.Name='%1'
ORDER BY Job.StartTime LIMIT 20;
# 2
:List where the most recent copies of a file are saved
*Enter path with trailing slash:
*Enter filename:
*Enter Client name:
SELECT DISTINCT Job.JobId,StartTime AS JobStartTime,VolumeName,Client.Name
AS ClientName
FROM Job,File,Path,Filename,Media,JobMedia,Client
WHERE File.JobId=Job.JobId
AND Path.Path='%1'
AND Filename.Name='%2'
AND Client.Name='%3'
AND Path.PathId=File.PathId
AND Filename.FilenameId=File.FilenameId
AND JobMedia.JobId=Job.JobId
AND JobMedia.MediaId=Media.MediaId
AND Client.ClientId=Job.ClientId
ORDER BY Job.StartTime DESC LIMIT 5;
# 3
:List last 20 Full Backups for a Client
*Enter Client name:
SELECT DISTINCT Job.JobId,Client.Name AS Client,StartTime,JobFiles,JobBytes,
JobMedia.StartFile as VolFile,VolumeName
FROM Client,Job,JobMedia,Media
WHERE Client.Name='%1'
AND Client.ClientId=Job.ClientId
AND Level='F' AND JobStatus IN ('T', 'W')
AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
ORDER BY Job.StartTime DESC LIMIT 20;
# 4
:List all backups for a Client after a specified time
*Enter Client Name:
*Enter time in YYYY-MM-DD HH:MM:SS format:
SELECT DISTINCT Job.JobId,Client.Name as
Client,Level,StartTime,JobFiles,JobBytes,VolumeName
FROM Client,Job,JobMedia,Media
WHERE Client.Name='%1'
AND Client.ClientId=Job.ClientId
AND JobStatus IN ('T', 'W')
AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
AND Job.StartTime >= '%2'
ORDER BY Job.StartTime;
# 5
:List all backups for a Client
*Enter Client Name:
SELECT DISTINCT Job.JobId as JobId,Client.Name as Client,
FileSet.FileSet AS FileSet,Level,StartTime,
JobFiles,JobBytes,VolumeName
FROM Client,Job,JobMedia,Media,FileSet
WHERE Client.Name='%1'
AND Client.ClientId=Job.ClientId AND Job.Type='B'
AND Job.JobStatus IN ('T','W') AND Job.FileSetId=FileSet.FileSetId
AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
ORDER BY Job.StartTime;
# 6
:List Volume Attributes for a selected Volume
*Enter Volume name:
SELECT Slot,MaxVolBytes,VolCapacityBytes,VolStatus,Recycle,VolRetention,
VolUseDuration,MaxVolJobs,MaxVolFiles
FROM Media
WHERE VolumeName='%1';
# 7
:List Volumes used by selected JobId
*Enter JobId:
SELECT DISTINCT Job.JobId,VolumeName
FROM Job,JobMedia,Media
WHERE Job.JobId=%1
AND Job.JobId=JobMedia.JobId
AND JobMedia.MediaId=Media.MediaId;
# 8
:List Volumes to Restore All Files
*Enter Client Name:
!DROP TABLE temp;
!DROP TABLE temp2;
CREATE TABLE temp (JobId BIGINT NOT NULL,
JobTDate BIGINT,
ClientId BIGINT,
Level CHAR,
StartTime TEXT,
VolumeName TEXT,
StartFile BIGINT,
VolSessionId BIGINT,
VolSessionTime BIGINT );
CREATE TABLE temp2 (JobId BIGINT NOT NULL,
StartTime TEXT,
VolumeName TEXT,
Level CHAR,
StartFile BIGINT,
VolSessionId BIGINT,
VolSessionTime BIGINT);
# Select last Full save
INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,
StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime
FROM Client,Job,JobMedia,Media WHERE Client.Name='%1'
AND Client.ClientId=Job.ClientId
AND Level='F' AND JobStatus IN ('T', 'W')
AND JobMedia.JobId=Job.JobId
AND JobMedia.MediaId=Media.MediaId
ORDER BY Job.JobTDate DESC LIMIT 1;
# Copy into temp 2 getting all volumes of Full save
INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,Job.Level,
JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
FROM temp,Job,JobMedia,Media WHERE temp.JobId=Job.JobId
AND Job.Level='F' AND Job.JobStatus IN ('T', 'W')
AND JobMedia.JobId=Job.JobId
AND JobMedia.MediaId=Media.MediaId;
# Now add subsequent incrementals
INSERT INTO temp2 SELECT DISTINCT Job.JobId,Job.StartTime,Media.VolumeName,
Job.Level,JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
FROM Job,temp,JobMedia,Media
WHERE Job.JobTDate>temp.JobTDate
AND Job.ClientId=temp.ClientId
AND Job.Level IN ('I','D') AND JobStatus IN ('T', 'W')
AND JobMedia.JobId=Job.JobId
AND JobMedia.MediaId=Media.MediaId;
# list results
SELECT DISTINCT VolumeName from temp2;
!DROP TABLE temp;
!DROP TABLE temp2;
# 9
:List Pool Attributes for a selected Pool
*Enter Pool name:
SELECT
Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes
FROM Pool
WHERE Name='%1';
# 10
:List total files/bytes by Job
SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,Name
AS Job
FROM Job GROUP by Name;
# 11
:List total files/bytes by Volume
SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS
Bytes,VolumeName
FROM Job,JobMedia,Media
WHERE JobMedia.JobId=Job.JobId
AND JobMedia.MediaId=Media.MediaId
GROUP by VolumeName;
# 12
:List Files for a selected JobId
*Enter JobId:
SELECT Path.Path,Filename.Name FROM File,Filename,Path WHERE File.JobId=%1
AND Filename.FilenameId=File.FilenameId
AND Path.PathId=File.PathId ORDER BY
Path.Path,Filename.Name;
# 13
:List Jobs stored on a selected MediaId
*Enter MediaId:
SELECT DISTINCT Job.JobId,Job.Name,Job.StartTime,Job.Type,
Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus
FROM JobMedia,Job
WHERE JobMedia.JobId=Job.JobId
AND JobMedia.MediaId=%1
ORDER by Job.StartTime;
# 14
:List Jobs stored for a given Volume name
*Enter Volume name:
SELECT DISTINCT Job.JobId as JobId,Job.Name as Name,Job.StartTime as
StartTime,
Job.Type as Type,Job.Level as Level,Job.JobFiles as Files,
Job.JobBytes as Bytes,Job.JobStatus as Status
FROM Media,JobMedia,Job
WHERE Media.VolumeName='%1'
AND Media.MediaId=JobMedia.MediaId
AND JobMedia.JobId=Job.JobId
ORDER by Job.StartTime;
# 15
:List Volumes Bacula thinks are in changer
SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024) AS GB,Storage.Name
AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus
FROM Media,Pool,Storage
WHERE Media.PoolId=Pool.PoolId
AND Slot>0 AND InChanger=1
AND Media.StorageId=Storage.StorageId
ORDER BY MediaType ASC, Slot ASC;
# 16
:List Volumes likely to need replacement from age or errors
SELECT VolumeName AS Volume,VolMounts AS Mounts,VolErrors AS Errors,
VolWrites AS Writes,VolStatus AS Status
FROM Media
WHERE (VolErrors>0) OR (VolStatus='Error') OR (VolMounts>50) OR
(VolStatus='Disabled') OR (VolWrites>3999999)
ORDER BY VolStatus ASC, VolErrors,VolMounts,VolumeName DESC;
# 17
:List Volumes Bacula thinks are eligible for the changer
SELECT VolumeName,VolStatus,Storage.Name AS Location,
VolBytes/(1024*1024*1024) AS GB,MediaId,MediaType,Pool.Name AS Pool
FROM Media,Pool,Storage
WHERE Media.PoolId=Pool.PoolId
AND Media.StorageId=Storage.StorageId
AND InChanger=0
AND ((VolStatus='Purged') OR (VolStatus='Append') OR
(VolStatus='Recycle'))
ORDER BY VolMounts ASC, Pool.Name ASC, VolumeName ASC
# 18
:List Volumes by Volume:
SELECT VolumeName, Job.JobId as JobID, Job.Name as JobName, Job.StartTime as
Start, sum(JobFiles) AS Files,sum(JobBytes) AS Bytes
FROM Job,JobMedia,Media
WHERE JobMedia.JobId=Job.JobId
AND JobMedia.MediaId=Media.MediaId
GROUP by VolumeName, Job.JobID, Job.Name, Job.StartTime
ORDER by VolumeName;
# 19
:List Volumes by Jobs:
SELECT Job.Name as JobName, Job.JobId as JobID, VolumeName, Job.StartTime as
Start, sum(JobFiles) AS Files,sum(JobBytes) AS Bytes
FROM Job,JobMedia,Media
WHERE JobMedia.JobId=Job.JobId
AND JobMedia.MediaId=Media.MediaId
GROUP by VolumeName, Job.JobID, Job.Name, Job.StartTime
ORDER by JobName, Start;
# 20
:List Volumes for a jobname:
*Enter Job name:
SELECT Job.Name as JobName, Job.JobId as JobID, VolumeName, Job.StartTime as
Start, sum(JobFiles) AS Files,sum(JobBytes) AS Bytes
FROM Job,JobMedia,Media
WHERE Job.Name='%1'
AND JobMedia.JobId=Job.JobId
AND JobMedia.MediaId=Media.MediaId
GROUP by VolumeName, Job.JobID, Job.Name, Job.StartTime
ORDER by JobName, Start;
*# cat /usr/lib64/bacula/make_catalog_backup.pl*
#!/usr/bin/env perl
use strict;
=head1 SCRIPT
This script dumps your Bacula catalog in ASCII format
It works for MySQL, SQLite, and PostgreSQL
=head1 USAGE
make_catalog_backup.pl MyCatalog
=head1 LICENSE
Bacula? - The Network Backup Solution
Copyright (C) 2000-2010 Free Software Foundation Europe e.V.
The main author of Bacula is Kern Sibbald, with contributions from
many others, a complete list can be found in the file AUTHORS.
This program is Free Software; you can redistribute it and/or
modify it under the terms of version three of the GNU Affero General
Public
License as published by the Free Software Foundation plus additions
that are listed in the file LICENSE.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
General Public License for more details.
You should have received a copy of the GNU Affero General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
02110-1301, USA.
Bacula? is a registered trademark of Kern Sibbald.
The licensor of Bacula is the Free Software Foundation Europe
(FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zurich,
Switzerland, email:f...@fsfeurope.org.
=cut
my $cat = shift or die "Usage: $0 catalogname";
my $dir_conf='/usr/sbin/dbcheck.mysql -B -c /etc/bacula/bacula-dir.conf';
my $wd = "/var/lib/bacula";
sub dump_sqlite3
{
my %args = @_;
exec("echo .dump | sqlite3 '$wd/$args{db_name}.db' >
'$wd/$args{db_name}.sql'");
print "Error while executing sqlite dump $!\n";
return 1;
}
# TODO: use just ENV and drop the pg_service.conf file
sub dump_pgsql
{
my %args = @_;
umask(0077);
if ($args{db_address}) {
$ENV{PGHOST}=$args{db_address};
}
if ($args{db_socket}) {
$ENV{PGHOST}=$args{db_socket};
}
if ($args{db_port}) {
$ENV{PGPORT}=$args{db_port};
}
if ($args{db_user}) {
$ENV{PGUSER}=$args{db_user};
}
if ($args{db_password}) {
$ENV{PGPASSWORD}=$args{db_password};
}
$ENV{PGDATABASE}=$args{db_name};
exec("HOME='$wd' pg_dump -c > '$wd/$args{db_name}.sql'");
print "Error while executing postgres dump $!\n";
return 1; # in case of error
}
sub dump_mysql
{
my %args = @_;
umask(0077);
unlink("$wd/.my.cnf");
open(MY, ">$wd/.my.cnf")
or die "Can't open $wd/.my.cnf for writing $@";
$args{db_address} = $args{db_address} || "localhost";
my $addr = "host=$args{db_address}";
if ($args{db_socket}) { # unix socket is fastest than net socket
$addr = "socket=$args{db_socket}";
}
print MY "[client]
$addr
user=$args{db_user}
password=$args{db_password}
";
if ($args{db_port}) {
print MY "port=$args{db_port}\n";
}
close(MY);
exec("HOME='$wd' mysqldump -f --opt $args{db_name} >
'$wd/$args{db_name}.sql'");
print "Error while executing mysql dump $!\n";
return 1;
}
sub dump_catalog
{
my %args = @_;
if ($args{db_type} eq 'SQLite3') {
$ENV{PATH}="/usr/bin:$ENV{PATH}";
dump_sqlite3(%args);
} elsif ($args{db_type} eq 'PostgreSQL') {
$ENV{PATH}="/usr/lib/postgresql91/bin:$ENV{PATH}";
dump_pgsql(%args);
} elsif ($args{db_type} eq 'MySQL') {
$ENV{PATH}="/usr/bin:$ENV{PATH}";
dump_mysql(%args);
} else {
die "This database type isn't supported";
}
}
open(FP, "$dir_conf -C '$cat'|") or die "Can't get catalog information $@";
# catalog=MyCatalog
# db_type=mysql
# db_name=regress
# db_driver=
# db_user=regress
# db_password=
# db_address=
# db_port=0
# db_socket=
my %cfg;
while(my $l = <FP>)
{
if ($l =~ /catalog=(.+)/) {
if (exists $cfg{catalog} and $cfg{catalog} eq $cat) {
exit dump_catalog(%cfg);
}
%cfg = (); # reset
}
if ($l =~ /(\w+)=(.+)/) {
$cfg{$1}=$2;
}
}
if (exists $cfg{catalog} and $cfg{catalog} eq $cat) {
exit dump_catalog(%cfg);
}
print "Can't find your catalog ($cat) in director configuration\n";
exit 1;
On Mon, Mar 4, 2013 at 1:44 PM, compdoc <comp...@hotrodpc.com> wrote:
> >hmmm....I see your point, apparently the file does exist. This is what I
> did:****
>
> ** **
>
> ** **
>
> > # find / -name 'query.sql'****
>
> >/usr/lib64/bacula/query.sql****
>
> ** **
>
> ** **
>
> >:/usr/lib64/bacula # ls****
>
> >bacula-password-setup.sh create_bacula_database drop_bacula_database
> dvd-handler make_catalog_backup query.sql****
>
> >bconsole create_mysql_database drop_bacula_tables
> grant_bacula_privileges make_catalog_backup.pl update_bacula_tables****
>
> >bpipe-fd.so delete_catalog_backup drop_mysql_database
> grant_mysql_privileges make_mysql_tables updatedb****
>
> >btraceback.gdb disk-changer drop_mysql_tables
> make_bacula_tables mtx-changer update_mysql_tables****
>
> ** **
>
> >The only thing different is that all files are green, query.sql is
> black......****
>
> ** **
>
> ** **
>
> >Carlos****
>
> ** **
>
> ** **
>
> ** **
>
> What do you see when you type: cat /usr/lib64/bacula/query.sql****
>
> And what do you see when: cat /usr/lib64/bacula/make_catalog_backup.pl***
> *
>
> Or any of the green colored files?****
>
> ** **
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_feb
> _______________________________________________
> Bacula-users mailing list
> Bacula-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/bacula-users
>
>
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
