SQL Backup Report with breakdown DB backed up

[ESM_Admin]

the generic reports I have in VBR allow me to run the backup reports for backup policy as shown below.

Job Client	Job Group ID	Job Type	Policy	Schedule	Filesystem Name	Job Start Time	Job End Time	Job Size (MB)	Status	Job Error Code

dc520	1075224	Backup	DC520-SQL	Default-Application-Backup	MS-SQL-Server Backup	Mar 01, 2010 19:18:31	Mar 01, 2010 19:19:10	1.34	Success	0
dc520	1075040	Backup	DC520-SQL	Default-Application-Backup	MS-SQL-Server Backup	Mar 01, 2010 17:56:00	Mar 01, 2010 17:56:39	2.53	Success	0

How can I setup the reports to show me WHICH DB was backed up as below:


JOBID  CLASS NAME      SCHEDULE NAME   SERVER NAME     START TIME           END TIME             DURATION INCLUDES                                           Size (Kb)    Nbr Files STATUS
------ --------------- --------------- --------------- -------------------- -------------------- -------- -------------------------------------------------- ------------ --------- ------
990312 DC520-SQL           Full_SQL        DC520     11/5/2009 5:12:55 PM 11/5/2009 7:33:01 PM 02h20    D:\VERITAS\NetBackup\DbExt\MsSql\Daily_DC520.bch;D:\VERITAS\NetBackup\DbExt\MsSql\Daily_SOX_01.bch                                                          0         0 Failed(None of the requested files were backed up.)
990314 DC520-SQL           Default-Applica NDC520     11/5/2009 5:13:13 PM 11/5/2009 5:38:44 PM 990328 DC520-SQL           Default-Applica DC520     11/5/2009 5:39:27 PM 11/5/2009 5:39:27 PM 00h00    /DC520.MSSQL7.DC520.db.BAMPrimaryImport.~.7.001of001.20091105173924..C                                                                                0         0 Failed(The required storage unit is unavailable.)
990329 DC520-SQL           Default-Applica TOROONDC520     11/5/2009 5:39:40 PM 11/5/2009 5:39:40 PM 00h00    /DC520.MSSQL7.NDC520.db.Tell.~.7.001of001.20091105173937..C                                                                                            0         0 Failed(The required storage unit is unavailable.)


The above report is generated from the scripts, but I cannot duplicate it on VBR.

any assistance i get will be appreciated.

thanks

Vic

[payners]

You can run this to see what VBR is grabbing. You will need on the breakupjobs option for each module though.  This report will show the client and  the first 6 characters of the "backup selection". If you need more characters just increase the 6 in the select portion and the group by portion at the bottom.  You will also need to change the following  to reflect your sql policy and client name:

backuppolicyname like 'your-sql-policy' and
clientobject.objectprimarydisplayname like 'your-client'
______________________________________________________________________


select distinct


clientobject.objectprimarydisplayname as 'Client',
substring(directoryobject.objectprimarydisplayname,0,6) as 'Database',


from

backupfinaljob,
backupsubjob,
backuppolicy,
object as clientobject,

where

backupfinaljobclientobjectid=clientobject.objectid and
backupsubjobbackupfinaljobid=backupfinaljobid and
backupfinaljobstarttime>=(now()-30) and
backupsubjobdirectoryobjectid=directoryobject.objectid and
backupfinaljobbackuppolicyid=backuppolicyid and
backuppolicyname like 'your-sql-policy' and
clientobject.objectprimarydisplayname like 'your-client'

group by

clientobject.objectprimarydisplayname,
directoryobject.objectprimarydisplayname