Forum Discussion

Maialen_Zufiaur's avatar
Maialen_Zufiaur
Level 4
11 years ago

OpsCenter Vault Query

Hello

I need a report query to show the number of copies from a backup Image.  I need to know if there is a image without vaulting.

I'm lookuing for that in "domain_image" table but i'm not sure

Thanks in advance

 

AIX
Backup and Recovery
NetBackup
Reporting
  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    11 years ago

    Hi,

     

    I looked at this a bit and its a bit complicated without knowing your duplication policy. We can look at domain_image.unexpiredCopyCount which will show you how many copies you have. So one could assume if its less than two you've not done the vault (the number depends on your number of copies). But then, depending on retention, it could also mean that the vault was done a long time ago, and now the primary (shorter retention) has lapsed so only one copy remains.

    So to eliminate that you could add writeEndTime to only look at the last day (or 7)

     

    select * from domain_image where domain_image.unexpiredCopyCount = 1
    and DATEDIFF(day,UTCBigIntToNomTime(domain_image.writeEndTime), GETDATE()) <= 7

     

    Thoughts?

  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    11 years ago

    Hi,

     

    I looked at this a bit and its a bit complicated without knowing your duplication policy. We can look at domain_image.unexpiredCopyCount which will show you how many copies you have. So one could assume if its less than two you've not done the vault (the number depends on your number of copies). But then, depending on retention, it could also mean that the vault was done a long time ago, and now the primary (shorter retention) has lapsed so only one copy remains.

    So to eliminate that you could add writeEndTime to only look at the last day (or 7)

     

    select * from domain_image where domain_image.unexpiredCopyCount = 1
    and DATEDIFF(day,UTCBigIntToNomTime(domain_image.writeEndTime), GETDATE()) <= 7

     

    Thoughts?

  • watsons's avatar
    watsons
    Level 6
    11 years ago

    A simple query to get image & its number of copies is (note this assumes you don't have AIR replication as it may be different in output) :

    select imageid, count(id) from domain_imageCopy  group by imageid order by imageid

    unexpiredCopyCount field in domain_image table, according to Schema notes, is referring to the "The number of fragments that make up a complete unexpired backup". Confusing name I know, but not the number of copies you want.

    The vault you mentioned, is it specific to netbackup vault feature that we can assume it's a tape? If so, this query may involve domain_imageFragment as well, because different fragments could be in a different tape mediaID. I will try to test to see if can get the above query to include mediaID (and if it's been vaulted)

  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    11 years ago

    Hi Watsons,

     

    If you have some examples please share it. I only have a small test environment. If i run a backup and duplicate it the unexpiredcopyCount increases each time, but then again because its small I might only have 1 FRAG per copy (same as seen in bpimagelist)

     

  • watsons's avatar
    watsons
    Level 6
    11 years ago

    Turn out I am wrong , Sorry Riaan, the schema guide seems to confuse me instead... I have an image with 8 fragments, but unexpiredCopyCount came out as "1", not "8". Not sure why the description of that field is "number of fragments that makes up..."

    I can't copy the table, so here is the result:

    id  |    unexpiredCopyCount

    w2k3bcd_1416733472    1

    The bpimagelist is like:

    Client:            w2k3bcd
    Backup ID:         w2k3bcd_1416733472
    Policy:            Wins
    Policy Type:       MS-Windows (13)
    Number of Copies:  1
    Number of Fragments:   8

     

    Btw, it's not hard to create a multiple-fragments image. Just prepare a 2~5GB data for backup, reduce the fragment size of disk storage unit to 1 or 2GB and there you go.