2 # This file contains sample queries that you can possibly use in
3 # your query.sql file. However, please note that these examples
4 # are not supported. They may or may not work -- in fact, they may
5 # work with one SQL engine and not another.
7 # If you find that they work for MySQL and not for PostgreSQL,
8 # then please send us a whole new file corrected for PostgreSQL
9 # and we will post it here for everyone to use.
13 :List up to 20 places where a File is saved regardless of the directory
14 *Enter Filename (no path):
15 SELECT DISTINCT Job.JobId as JobId, Client.Name as Client,
16 Path.Path,Filename.Name,StartTime,Level,JobFiles,JobBytes
17 FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId
18 AND JobStatus='T' AND Job.JobId=File.JobId
19 AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId
20 AND Filename.Name='%1'
21 ORDER BY Job.StartTime LIMIT 20;
23 :List where the most recent copies of a file are saved
24 *Enter path with trailing slash:
27 SELECT DISTINCT Job.JobId,StartTime AS JobStartTime,VolumeName,Client.Name AS ClientName
28 FROM Job,File,Path,Filename,Media,JobMedia,Client
29 WHERE File.JobId=Job.JobId
31 AND Filename.Name='%2'
33 AND Path.PathId=File.PathId
34 AND Filename.FilenameId=File.FilenameId
35 AND JobMedia.JobId=Job.JobId
36 AND JobMedia.MediaId=Media.MediaId
37 AND Client.ClientId=Job.ClientId
38 ORDER BY Job.StartTime DESC LIMIT 5;
40 :List last 20 Full Backups for a Client
42 SELECT DISTINCT Job.JobId,Client.Name AS Client,StartTime,JobFiles,JobBytes,
43 JobMedia.StartFile as VolFile,VolumeName
44 FROM Client,Job,JobMedia,Media
45 WHERE Client.Name='%1'
46 AND Client.ClientId=Job.ClientId
47 AND Level='F' AND JobStatus='T'
48 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
49 ORDER BY Job.StartTime DESC LIMIT 20;
51 :List all backups for a Client after a specified time
53 *Enter time in YYYY-MM-DD HH:MM:SS format:
54 SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,JobBytes,VolumeName
55 FROM Client,Job,JobMedia,Media
56 WHERE Client.Name='%1'
57 AND Client.ClientId=Job.ClientId
59 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
60 AND Job.StartTime >= '%2'
61 ORDER BY Job.StartTime;
63 :List all backups for a Client
65 SELECT DISTINCT Job.JobId as JobId,Client.Name as Client,
66 FileSet.FileSet AS FileSet,Level,StartTime,
67 JobFiles,JobBytes,VolumeName
68 FROM Client,Job,JobMedia,Media,FileSet
69 WHERE Client.Name='%1'
70 AND Client.ClientId=Job.ClientId AND Job.Type='B'
71 AND Job.JobStatus='T' AND Job.FileSetId=FileSet.FileSetId
72 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
73 ORDER BY Job.StartTime;
75 :List Volume Attributes for a selected Volume
77 SELECT Slot,MaxVolBytes,VolCapacityBytes,VolStatus,Recycle,VolRetention,
78 VolUseDuration,MaxVolJobs,MaxVolFiles
80 WHERE VolumeName='%1';
82 :List Volumes used by selected JobId
84 SELECT DISTINCT Job.JobId,VolumeName
85 FROM Job,JobMedia,Media
87 AND Job.JobId=JobMedia.JobId
88 AND JobMedia.MediaId=Media.MediaId;
90 :List Volumes to Restore All Files
94 CREATE TABLE temp (JobId BIGINT NOT NULL,
102 VolSessionTime BIGINT );
103 CREATE TABLE temp2 (JobId BIGINT NOT NULL,
109 VolSessionTime BIGINT);
110 # Select last Full save
111 INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,
112 StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime
113 FROM Client,Job,JobMedia,Media WHERE Client.Name='%1'
114 AND Client.ClientId=Job.ClientId
115 AND Level='F' AND JobStatus='T'
116 AND JobMedia.JobId=Job.JobId
117 AND JobMedia.MediaId=Media.MediaId
118 ORDER BY Job.JobTDate DESC LIMIT 1;
119 # Copy into temp 2 getting all volumes of Full save
120 INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,Job.Level,
121 JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
122 FROM temp,Job,JobMedia,Media WHERE temp.JobId=Job.JobId
123 AND Job.Level='F' AND Job.JobStatus='T'
124 AND JobMedia.JobId=Job.JobId
125 AND JobMedia.MediaId=Media.MediaId;
126 # Now add subsequent incrementals
127 INSERT INTO temp2 SELECT DISTINCT Job.JobId,Job.StartTime,Media.VolumeName,
128 Job.Level,JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
129 FROM Job,temp,JobMedia,Media
130 WHERE Job.JobTDate>temp.JobTDate
131 AND Job.ClientId=temp.ClientId
132 AND Job.Level IN ('I','D') AND JobStatus='T'
133 AND JobMedia.JobId=Job.JobId
134 AND JobMedia.MediaId=Media.MediaId;
136 SELECT DISTINCT VolumeName from temp2;
140 :List Pool Attributes for a selected Pool
142 SELECT Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes
146 :List total files/bytes by Job
147 SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,Name AS Job
148 FROM Job GROUP by Name;
150 :List total files/bytes by Volume
151 SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,VolumeName
152 FROM Job,JobMedia,Media
153 WHERE JobMedia.JobId=Job.JobId
154 AND JobMedia.MediaId=Media.MediaId
157 :List Files for a selected JobId
159 SELECT Path.Path,Filename.Name FROM File,Filename,Path WHERE File.JobId=%1
160 AND Filename.FilenameId=File.FilenameId
161 AND Path.PathId=File.PathId ORDER BY
162 Path.Path,Filename.Name;
164 :List Jobs stored on a selected MediaId
166 SELECT DISTINCT Job.JobId,Job.Name,Job.StartTime,Job.Type,
167 Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus
169 WHERE JobMedia.JobId=Job.JobId
170 AND JobMedia.MediaId=%1
171 ORDER by Job.StartTime;
173 :List Jobs stored for a given Volume name
175 SELECT DISTINCT Job.JobId as JobId,Job.Name as Name,Job.StartTime as StartTime,
176 Job.Type as Type,Job.Level as Level,Job.JobFiles as Files,
177 Job.JobBytes as Bytes,Job.JobStatus as Status
178 FROM Media,JobMedia,Job
179 WHERE Media.VolumeName='%1'
180 AND Media.MediaId=JobMedia.MediaId
181 AND JobMedia.JobId=Job.JobId
182 ORDER by Job.StartTime;
184 :List Volumes Bacula thinks are in changer
185 SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024) AS GB,Storage.Name
186 AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus
187 FROM Media,Pool,Storage
188 WHERE Media.PoolId=Pool.PoolId
189 AND Slot>0 AND InChanger=1
190 AND Media.StorageId=Storage.StorageId
191 ORDER BY MediaType ASC, Slot ASC;
193 :List Volumes likely to need replacement from age or errors
194 SELECT VolumeName AS Volume,VolMounts AS Mounts,VolErrors AS Errors,
195 VolWrites AS Writes,VolStatus AS Status
197 WHERE (VolErrors>0) OR (VolStatus='Error') OR (VolMounts>50) OR
198 (VolStatus='Disabled') OR (VolWrites>3999999)
199 ORDER BY VolStatus ASC, VolErrors,VolMounts,VolumeName DESC;
201 :List Volumes Bacula thinks are eligible for the changer
202 SELECT VolumeName,VolStatus,Storage.Name AS Location,
203 VolBytes/(1024*1024*1024) AS GB,MediaId,MediaType,Pool.Name AS Pool
204 FROM Media,Pool,Storage
205 WHERE Media.PoolId=Pool.PoolId
206 AND Media.StorageId=Storage.StorageId
208 AND ((VolStatus='Purged') OR (VolStatus='Append') OR (VolStatus='Recycle'))
209 ORDER BY VolMounts ASC, Pool.Name ASC, VolumeName ASC