SQL query to Commserv database

Last post 02-08-2019, 6:35 AM by CharlesL. 3 replies.
Sort Posts: Previous Next
  • SQL query to Commserv database
    Posted: 02-06-2019, 8:41 AM

    I need a SQL query to the Commserv database to retreive the backupset name that is associated with a backup client. I'm using this in a workflow to decommission VADP clients.

     

    Thanks


    Charles Lavender
  • SQL query to Commserv database
    Posted: 02-06-2019, 9:41 AM
    SET NOCOUNT ON
    use CommServ
    SELECT 'det'
    -- ,clientId,
    ,ac.name as 'client name'
    -- ,appTypeId
    ,ai.name as 'apptype name'
    -- ,instance
    ,an.name as 'instance name'
    -- ,backupset
    ,ab.name as 'backupset name'
    -- ,aa.id
    , subClientName
    -- , subClientStatus
    FROM CommServ.dbo.app_application aa with (readuncommitted)
    join CommServ.dbo.app_client ac with (readuncommitted) on ac.id=aa.clientId
    join CommServ.dbo.app_iDAType ai with (readuncommitted) on ai.type=aa.appTypeId
    join CommServ.dbo.app_instanceName an with (readuncommitted) on an.id=aa.instance
    join CommServ.dbo.app_backupSetName ab with (readuncommitted) on ab.id=aa.backupSet
    WHERE appTypeId > 0 and appTypeId AND CommServ.dbo.IsSubClientValid(appTypeId,subclientStatus,0)=1
  • Re: SQL query to Commserv database
    Posted: 02-07-2019, 2:20 PM

    So basically we are looking for a relationship to SubClient Tree

     

    SET NOCOUNT ON

    use CommServ

    SELECT 'det'

    -- ,clientId,

    ,ac.name as 'client name'

    -- ,appTypeId

    ,ai.name as 'apptype name'

    -- ,instance

    ,an.name as 'instance name'

    -- ,backupset

    ,ab.name as 'backupset name'

    -- ,aa.id

    , subClientName

    -- , subClientStatus

    FROM CommServ.dbo.app_application aa with (readuncommitted)

    join CommServ.dbo.app_client ac with (readuncommitted) on ac.id=aa.clientId

    join CommServ.dbo.app_iDAType ai with (readuncommitted) on ai.type=aa.appTypeId

    join CommServ.dbo.app_instanceName an with (readuncommitted) on an.id=aa.instance

    join CommServ.dbo.app_backupSetName ab with (readuncommitted) on ab.id=aa.backupSet

    --WHERE appTypeId > 0

    --and appTypeId = 0

    --AND CommServ.dbo.IsSubClientValid(appTypeId,subclientStatus,0)=1

    where ai.name = 'Virtual Server'

     

     


    Charles Lavender
  • Re: SQL query to Commserv database
    Posted: 02-08-2019, 6:35 AM

    The last line of this code errors out. 

    I have 7 backupsets under a virtual server and each backupset contains 200 VMs. So I need a SQL query to identify the backupset that contains the vm that I want to decommission. The SQL query below doesn't work. I'm running VADP backups not iDataAgents.

     

     

    SET NOCOUNT ON 
    use CommServ 
    SELECT 'det' 
    -- ,clientId, 
    ,ac.name as 'client name' 
    -- ,appTypeId 
    ,ai.name as 'apptype name' 
    -- ,instance 
    ,an.name as 'instance name' 
    -- ,backupset 
    ,ab.name as 'backupset name' 
    -- ,aa.id 
    , subClientName 
    -- , subClientStatus 
    FROM CommServ.dbo.app_application aa with (readuncommitted) 
    join CommServ.dbo.app_client ac with (readuncommitted) on ac.id=aa.clientId 
    join CommServ.dbo.app_iDAType ai with (readuncommitted) on ai.type=aa.appTypeId 
    join CommServ.dbo.app_instanceName an with (readuncommitted) on an.id=aa.instance 
    join CommServ.dbo.app_backupSetName ab with (readuncommitted) on ab.id=aa.backupSet 
    WHERE appTypeId > 0 and appTypeId AND CommServ.dbo.IsSubClientValid(appTypeId,subclientStatus,0)=1


    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