SQL query to get the last full successful full backup for a iDataAgent backup.

Last post 07-23-2019, 6:23 AM by CharlesL. 4 replies.
Sort Posts: Previous Next
  • SQL query to get the last full successful full backup for a iDataAgent backup.
    Posted: 07-18-2019, 9:27 AM

    I have a SQL query listed below that gives me the last successful full backup within the last 7 days for a VADP VSA VM backup. However, now I need this query to give me the backups for a server with an iDataAgent backup. Any help with modifying this query would be appreciated.

    select DISTINCT vm.clientname

                               ,vm.idataagent

                               ,vm.instance

                               ,vm.backupset

                               ,vm.subclient

                               ,vm.data_sp

                               ,max(dateadd(s,vm.startdateunixsec,'1970-01-01 07:00:00')) as 'Last Successful Backup'

                               ,vm.backuplevel

                               ,max(vm.jobid) as 'Job id'                          

    from CommCellBackupInfo vm INNER JOIN APP_VMPROP app on vm.jobid = app.jobId

                                                INNER JOIN APP_ClientGroupAssoc a ON app.VMclientId = a.clientId

                                                INNER JOIN APP_Client c on app.VMclientId = c.id

    where startdate >= (dateadd(dd,-30,GETDATE())) /*and clientname = '$g'*/ and jobstatus = 'Success'

      and a.clientGroupId in (Select id From APP_ClientGroup Where name = 'AutomationGroup')

    GROUP BY clientname,idataagent,instance,backupset,subclient,data_sp,backuplevel

    UNION

    select DISTINCT vm.vmname as clientname

                               ,'Virtual Server' as idataagent

                               ,vm.virtualizationclient as instance

                               ,vm.backupset

                               ,vm.subclient

                               ,vm.data_sp

                               ,max(dateadd(s,convert(int,vm.startdateunixsec),'1970-01-01 07:00:00')) as 'Last Successful Backup'

                               ,vm.backuplevel

                               ,max(vm.jobid) as 'Job id'                     

    from CommCellVMBackupInfo vm INNER JOIN APP_ClientGroupAssoc a ON vm.vmclientid = a.clientId

    inner join APP_Client c on vm.vmclientid = c.id

    where startdate >= (dateadd(dd,-30,GETDATE())) /*and vmname = 'aauth02apwxa'*/ and vmstatus = 'Success'

      and a.clientGroupId in (Select id From APP_ClientGroup Where name = 'AutomationGroup')

    GROUP BY vmname,virtualizationclient,backupset,subclient,data_sp,backuplevel

     

     

     


    Charles Lavender
  • Re: SQL query to get the last full successful full backup for a iDataAgent backup.
    Posted: 07-19-2019, 2:24 AM

    What you are looking for is located in the [CommCellBackupInfo] view.

  • Re: SQL query to get the last full successful full backup for a iDataAgent backup.
    Posted: 07-22-2019, 10:00 AM

    I modified the query but I still can't get a backup report for the servers with iDataAgent backups. New SQL query below.

    select DISTINCT clientname
    ,clientname
    ,idataagent
    ,backupset
    ,subclient
    ,instance
    ,vm.data_sp
    ,max(dateadd(s,vm.startdateunixsec,'1970-01-01 07:00:00')) as 'Last Successful Backup'
    ,vm.backuplevel
    ,max(vm.jobid) as 'Job id'
    from CommCellBackupInfo vm INNER JOIN APP_VMPROP app on vm.jobid = app.jobId
    INNER JOIN APP_ClientGroupAssoc a ON app.VMclientId = a.clientId
    --INNER JOIN APP_Client c on app.vmclientId = c.id
    where startdate >= (dateadd(dd,-30,GETDATE())) and jobstatus = 'Success'
    and a.clientGroupId in (Select id From APP_ClientGroup Where name = 'AutomationGroup')
    GROUP BY clientname,idataagent,instance,backupset,subclient,data_sp,backuplevel

    Charles Lavender
  • Re: SQL query to get the last full successful full backup for a iDataAgent backup.
    Posted: 07-22-2019, 11:01 AM

    You will need one query to the MAX Date from CommCellBackupInfo for a VM Client ID where the idataagent/subclient equals virtual server agent, plus another query for the MAX Date for the Client ID where the idataagent/subclient equals File System.  Then join these two in query that returns the max date from both queries.

    For example (it is late here and I don't have a cv ssms in front of me, so pardon the heavy use of psuedocode - you will need to heavily modify this :( sorry).

    SELECT V.ClientId, V.BackupDate, F.ClientId, F.BackupDate

    FROM (

    SELECT max(vm.jobdate) BackupDate, VM.clientid

    FROM commcellbackupinfo vm

    INNER JOIN APP_CLIENT GROUP ASSOCIATION a ON vm.clientId = a.clientId

    where startdate >= (dateadd(dd,-30,GETDATE())) and jobstatus = 'Success'

    and a.clientGroupId in (Select id From APP_ClientGroup Where name = 'AutomationGroup')
    and vm.idataagent = "virtual server agent"
    GROUP BY VM.ClientId
    ) V
    LEFT JOIN (
    SELECT max(FS.jobdate) BackupDate, FS.clientid

    FROM commcellbackupinfo fs

    INNER JOIN APP_CLIENT GROUP ASSOCIATION a ON fs.clientId = a.clientId

    where startdate >= (dateadd(dd,-30,GETDATE())) and jobstatus = 'Success'

    and a.clientGroupId in (Select id From APP_ClientGroup Where name = 'AutomationGroup')
    and fs.idataagent = "Windows File System"
    GROUP BY FS.ClientId
    ) F ON V.clientid = F.clientid
  • Re: SQL query to get the last full successful full backup for a iDataAgent backup.
    Posted: 07-23-2019, 6:23 AM
    Anthony, thanks for the clarification. I received a SQL query from the CommVault SQL team that pulls all the backup information for all clients. However, the workflow will not pull in the results so that I can use them. Someone on the forum said to use the (SET NOCOUNT ON) but that didn’t resolve the problem. Why is it that I can see the results when I run the quey against the database in management studio but not in the workflow?
    Charles Lavender
The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Close
Copyright © 2019 Commvault | All Rights Reserved. | Legal | Privacy Policy