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.
9 :List up to 20 places where a File is saved regardless of the directory
10 *Enter Filename (no path):
11 SELECT DISTINCT Job.JobId as JobId, Client.Name as Client,
12 Path.Path,Filename.Name,StartTime,Level,JobFiles,JobBytes
13 FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId
14 AND JobStatus='T' AND Job.JobId=File.JobId
15 AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId
16 AND Filename.Name='%1'
17 ORDER BY Job.StartTime LIMIT 20;
19 :List where the most recent copies of a file are saved
20 *Enter path with trailing slash:
23 SELECT DISTINCT Job.JobId,StartTime AS JobStartTime,VolumeName,Client.Name AS ClientName
24 FROM Job,File,Path,Filename,Media,JobMedia,Client
25 WHERE File.JobId=Job.JobId
27 AND Filename.Name='%2'
29 AND Path.PathId=File.PathId
30 AND Filename.FilenameId=File.FilenameId
31 AND JobMedia.JobId=Job.JobId
32 AND JobMedia.MediaId=Media.MediaId
33 AND Client.ClientId=Job.ClientId
34 ORDER BY Job.StartTime DESC LIMIT 5;
36 :List last 20 Full Backups for a Client
38 SELECT DISTINCT Job.JobId,Client.Name AS Client,StartTime,JobFiles,JobBytes,
39 JobMedia.StartFile as VolFile,VolumeName
40 FROM Client,Job,JobMedia,Media
41 WHERE Client.Name='%1'
42 AND Client.ClientId=Job.ClientId
43 AND Level='F' AND JobStatus='T'
44 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
45 ORDER BY Job.StartTime DESC LIMIT 20;
47 :List all backups for a Client after a specified time
49 *Enter time in YYYY-MM-DD HH:MM:SS format:
50 SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,JobBytes,VolumeName
51 FROM Client,Job,JobMedia,Media
52 WHERE Client.Name='%1'
53 AND Client.ClientId=Job.ClientId
55 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
56 AND Job.StartTime >= '%2'
57 ORDER BY Job.StartTime;
59 :List all backups for a Client
61 SELECT DISTINCT Job.JobId as JobId,Client.Name as Client,
62 FileSet.FileSet AS FileSet,Level,StartTime,
63 JobFiles,JobBytes,VolumeName
64 FROM Client,Job,JobMedia,Media,FileSet
65 WHERE Client.Name='%1'
66 AND Client.ClientId=Job.ClientId AND Job.Type='B'
67 AND Job.JobStatus='T' AND Job.FileSetId=FileSet.FileSetId
68 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
69 ORDER BY Job.StartTime;
71 :List Volume Attributes for a selected Volume
73 SELECT Slot,MaxVolBytes,VolCapacityBytes,VolStatus,Recycle,VolRetention,
74 VolUseDuration,MaxVolJobs,MaxVolFiles
76 WHERE VolumeName='%1';
78 :List Volumes used by selected JobId
80 SELECT DISTINCT Job.JobId,VolumeName
81 FROM Job,JobMedia,Media
83 AND Job.JobId=JobMedia.JobId
84 AND JobMedia.MediaId=Media.MediaId;
86 :List Volumes to Restore All Files
90 CREATE TABLE temp (JobId BIGINT NOT NULL,
98 VolSessionTime BIGINT );
99 CREATE TABLE temp2 (JobId BIGINT NOT NULL,
105 VolSessionTime BIGINT);
106 # Select last Full save
107 INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,
108 StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime
109 FROM Client,Job,JobMedia,Media WHERE Client.Name='%1'
110 AND Client.ClientId=Job.ClientId
111 AND Level='F' AND JobStatus='T'
112 AND JobMedia.JobId=Job.JobId
113 AND JobMedia.MediaId=Media.MediaId
114 ORDER BY Job.JobTDate DESC LIMIT 1;
115 # Copy into temp 2 getting all volumes of Full save
116 INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,Job.Level,
117 JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
118 FROM temp,Job,JobMedia,Media WHERE temp.JobId=Job.JobId
119 AND Job.Level='F' AND Job.JobStatus='T'
120 AND JobMedia.JobId=Job.JobId
121 AND JobMedia.MediaId=Media.MediaId;
122 # Now add subsequent incrementals
123 INSERT INTO temp2 SELECT DISTINCT Job.JobId,Job.StartTime,Media.VolumeName,
124 Job.Level,JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
125 FROM Job,temp,JobMedia,Media
126 WHERE Job.JobTDate>temp.JobTDate
127 AND Job.ClientId=temp.ClientId
128 AND Job.Level IN ('I','D') AND JobStatus='T'
129 AND JobMedia.JobId=Job.JobId
130 AND JobMedia.MediaId=Media.MediaId;
132 SELECT DISTINCT VolumeName from temp2;
136 :List Pool Attributes for a selected Pool
138 SELECT Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes
142 :List total files/bytes by Job
143 SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,Name AS Job
144 FROM Job GROUP by Name;
146 :List total files/bytes by Volume
147 SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,VolumeName
148 FROM Job,JobMedia,Media
149 WHERE JobMedia.JobId=Job.JobId
150 AND JobMedia.MediaId=Media.MediaId
153 :List Files for a selected JobId
155 SELECT Path.Path,Filename.Name FROM File,Filename,Path WHERE File.JobId=%1
156 AND Filename.FilenameId=File.FilenameId
157 AND Path.PathId=File.PathId ORDER BY
158 Path.Path,Filename.Name;
160 :List Jobs stored on a selected MediaId
162 SELECT DISTINCT Job.JobId,Job.Name,Job.StartTime,Job.Type,
163 Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus
165 WHERE JobMedia.JobId=Job.JobId
166 AND JobMedia.MediaId=%1
167 ORDER by Job.StartTime;
169 :List Jobs stored for a given Volume name
171 SELECT DISTINCT Job.JobId as JobId,Job.Name as Name,Job.StartTime as StartTime,
172 Job.Type as Type,Job.Level as Level,Job.JobFiles as Files,
173 Job.JobBytes as Bytes,Job.JobStatus as Status
174 FROM Media,JobMedia,Job
175 WHERE Media.VolumeName='%1'
176 AND Media.MediaId=JobMedia.MediaId
177 AND JobMedia.JobId=Job.JobId
178 ORDER by Job.StartTime;
180 :List Volumes Bacula thinks are in changer
181 SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024) AS GB,Storage.Name
182 AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus
183 FROM Media,Pool,Storage
184 WHERE Media.PoolId=Pool.PoolId
185 AND Slot>0 AND InChanger=1
186 AND Media.StorageId=Storage.StorageId
187 ORDER BY MediaType ASC, Slot ASC;
189 :List Volumes likely to need replacement from age or errors
190 SELECT VolumeName AS Volume,VolMounts AS Mounts,VolErrors AS Errors,
191 VolWrites AS Writes,VolStatus AS Status
193 WHERE (VolErrors>0) OR (VolStatus='Error') OR (VolMounts>50) OR
194 (VolStatus='Disabled') OR (VolWrites>3999999)
195 ORDER BY VolStatus ASC, VolErrors,VolMounts,VolumeName DESC;