2 :List up to 20 places where a File is saved regardless of the directory
3 *Enter Filename (no path):
4 SELECT DISTINCT Job.JobId as JobId, Client.Name as Client,
5 Path.Path,Filename.Name,StartTime,Level,JobFiles,JobBytes
6 FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId
7 AND JobStatus='T' AND Job.JobId=File.JobId
8 AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId
10 ORDER BY Job.StartTime LIMIT 20;
12 :List where the most recent copies of a file are saved
13 *Enter path with trailing slash:
16 SELECT DISTINCT Job.JobId,StartTime AS JobStartTime,VolumeName,Client.Name AS ClientName
17 FROM Job,File,Path,Filename,Media,JobMedia,Client
18 WHERE File.JobId=Job.JobId
20 AND Filename.Name='%2'
22 AND Path.PathId=File.PathId
23 AND Filename.FilenameId=File.FilenameId
24 AND JobMedia.JobId=Job.JobId
25 AND JobMedia.MediaId=Media.MediaId
26 AND Client.ClientId=Job.ClientId
27 ORDER BY Job.StartTime DESC LIMIT 5;
29 :List last 20 Full Backups for a Client
31 SELECT DISTINCT Job.JobId,Client.Name AS Client,StartTime,JobFiles,JobBytes,
32 JobMedia.StartFile as VolFile,VolumeName
33 FROM Client,Job,JobMedia,Media
34 WHERE Client.Name='%1'
35 AND Client.ClientId=Job.ClientId
36 AND Level='F' AND JobStatus='T'
37 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
38 ORDER BY Job.StartTime DESC LIMIT 20;
40 :List all backups for a Client after a specified time
42 *Enter time in YYYY-MM-DD HH:MM:SS format:
43 SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,JobBytes,VolumeName
44 FROM Client,Job,JobMedia,Media
45 WHERE Client.Name='%1'
46 AND Client.ClientId=Job.ClientId
48 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
49 AND Job.StartTime >= '%2'
50 ORDER BY Job.StartTime;
52 :List all backups for a Client
54 SELECT DISTINCT Job.JobId as JobId,Client.Name as Client,
55 FileSet.FileSet AS FileSet,Level,StartTime,
56 JobFiles,JobBytes,VolumeName
57 FROM Client,Job,JobMedia,Media,FileSet
58 WHERE Client.Name='%1'
59 AND Client.ClientId=Job.ClientId
60 AND JobStatus='T' AND Job.FileSetId=FileSet.FileSetId
61 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
62 ORDER BY Job.StartTime;
64 :List Volume Attributes for a selected Volume
66 SELECT Slot,MaxVolBytes,VolCapacityBytes,VolStatus,Recycle,VolRetention,
67 VolUseDuration,MaxVolJobs,MaxVolFiles
69 WHERE VolumeName='%1';
71 :List Volumes used by selected JobId
73 SELECT DISTINCT Job.JobId,VolumeName
74 FROM Job,JobMedia,Media
76 AND Job.JobId=JobMedia.JobId
77 AND JobMedia.MediaId=Media.MediaId;
79 :List Volumes to Restore All Files
83 CREATE TABLE temp (JobId BIGINT NOT NULL,
91 VolSessionTime BIGINT );
92 CREATE TABLE temp2 (JobId BIGINT NOT NULL,
98 VolSessionTime BIGINT);
99 # Select last Full save
100 INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,
101 StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime
102 FROM Client,Job,JobMedia,Media WHERE Client.Name='%1'
103 AND Client.ClientId=Job.ClientId
104 AND Level='F' AND JobStatus='T'
105 AND JobMedia.JobId=Job.JobId
106 AND JobMedia.MediaId=Media.MediaId
107 ORDER BY Job.JobTDate DESC LIMIT 1;
108 # Copy into temp 2 getting all volumes of Full save
109 INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,Job.Level,
110 JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
111 FROM temp,Job,JobMedia,Media WHERE temp.JobId=Job.JobId
112 AND Job.Level='F' AND Job.JobStatus='T'
113 AND JobMedia.JobId=Job.JobId
114 AND JobMedia.MediaId=Media.MediaId;
115 # Now add subsequent incrementals
116 INSERT INTO temp2 SELECT DISTINCT Job.JobId,Job.StartTime,Media.VolumeName,
117 Job.Level,JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
118 FROM Job,temp,JobMedia,Media
119 WHERE Job.JobTDate>temp.JobTDate
120 AND Job.ClientId=temp.ClientId
121 AND Job.Level IN ('I','D') AND JobStatus='T'
122 AND JobMedia.JobId=Job.JobId
123 AND JobMedia.MediaId=Media.MediaId;
125 SELECT DISTINCT VolumeName from temp2;
129 :List Pool Attributes for a selected Pool
131 SELECT Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes
135 :List total files/bytes by Job
136 SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,Name AS Job
137 FROM Job GROUP by Name;
139 :List total files/bytes by Volume
140 SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,VolumeName
141 FROM Job,JobMedia,Media
142 WHERE JobMedia.JobId=Job.JobId
143 AND JobMedia.MediaId=Media.MediaId
146 :List Files for a selected JobId
148 SELECT Path.Path,Filename.Name FROM File,Filename,Path WHERE File.JobId=%1
149 AND Filename.FilenameId=File.FilenameId
150 AND Path.PathId=File.PathId ORDER BY
151 Path.Path,Filename.Name;
153 :List Jobs stored on a selected MediaId
155 SELECT DISTINCT Job.JobId,Job.Name,Job.StartTime,Job.Type,
156 Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus
158 WHERE JobMedia.JobId=Job.JobId
159 AND JobMedia.MediaId=%1
160 ORDER by Job.StartTime;
162 :List Jobs stored for a given Volume name
164 SELECT DISTINCT Job.JobId as JobId,Job.Name as Name,Job.StartTime as StartTime,
165 Job.Type as Type,Job.Level as Level,Job.JobFiles as Files,
166 Job.JobBytes as Bytes,Job.JobStatus as Status
167 FROM Media,JobMedia,Job
168 WHERE Media.VolumeName='%1'
169 AND Media.MediaId=JobMedia.MediaId
170 AND JobMedia.JobId=Job.JobId
171 ORDER by Job.StartTime;
173 :List Volumes Bacula thinks are in changer
174 SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024) AS GB,Storage.Name
175 AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus
176 FROM Media,Pool,Storage
177 WHERE Media.PoolId=Pool.PoolId
178 AND Slot>0 AND InChanger=1
179 AND Media.StorageId=Storage.StorageId
180 ORDER BY MediaType ASC, Slot ASC;
182 :List Volumes likely to need replacement from age or errors
183 SELECT VolumeName AS Volume,VolMounts AS Mounts,VolErrors AS Errors,
184 VolWrites AS Writes,VolStatus AS Status
186 WHERE (VolErrors>0) OR (VolStatus='Error') OR (VolMounts>50) OR
187 (VolStatus='Disabled') OR (VolWrites>3999999)
188 ORDER BY VolStatus ASC, VolErrors,VolMounts,VolumeName DESC;