Video Screencast Help

tool for checking archives

Created: 30 Apr 2013 • Updated: 03 Jun 2013 | 13 comments
This issue has been solved. See solution.

hello,

are there any tools or scripts out there that can check to see if an archive is working the way it should, we had an issue with a user, now we need to check them all.

i.e. archiving is working properly, retention catagories are bring applied correctly to the folders.

thanks

 

Operating Systems:

Comments 13 CommentsJump to latest comment

Rob.Wilcox's picture

Have a search for EVSVR, that may do what you're after.

AndrewB's picture

just curious to know if you found what you're looking for and if so can share your findings.

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner | www.trace3.com

TonySterling's picture

What version are you?  If running EV 10 sp2 there is a nice archiving report created after the schedule completes.

As for Retention categories, you could use this sql, it will run against the Vault Store database and you need to place the name of the archive where it says Your ArchiveName.

If you comment that line out it will run against all achives but may take some time.

SELECT A.ArchiveName
       ,CAST(AF.FolderPath AS NVARCHAR(MAX))
       ,AF.FolderName
       ,COUNT(S.IdTransaction) "Number of Archived Items"
       ,rc.RetentionCategoryName AS "Retention Category"
      -- ,SUM(S.ItemSize) "Total Size (KB)"
FROM   Saveset S,
       ArchivePoint Ap,
       Vault V,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Root R2,
       EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc
WHERE  S.VaultIdentity = V.VaultIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointID = R.VaultEntryID
  AND  V.VaultID = R2.VaultEntryID
  AND  R.RootIdentity = A.RootIdentity
  AND  R2.RootIdentity = AF.RootIdentity
  AND  rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
  AND  A.ArchiveName = 'Your ArchiveName'
GROUP BY A.ArchiveName, CAST(AF.FolderPath AS NVARCHAR(MAX)),AF.FolderName, rc.RetentionCategoryName

Chris Warren's picture

Tony - Nice Query!  

Correct me if I'm wrong, but it looks like this query will work only if the Vault Store and Directory DBs are on the same SQL instance, correct?

TonySterling's picture

Thanks Chris!

Yes, this is assuming that all the databases are on the same sql instance, if yours are spread out you would need to adjust accordingly.

Something like this I think will work:

--select * from archivefolder

SELECT A.ArchiveName
       ,CAST(AF.FolderPath AS NVARCHAR(MAX))
       ,AF.FolderName
       ,COUNT(S.IdTransaction) "Number of Archived Items"
       ,rc.RetentionCategoryName AS "Retention Category"
      -- ,SUM(S.ItemSize) "Total Size (KB)"
FROM   Saveset S,
       ArchivePoint Ap,
       Vault V,
       [SQLSERVER\Instance].EnterpriseVaultDirectory.dbo.Root R,
       [SQLSERVER\Instance].EnterpriseVaultDirectory.dbo.Root R2,
       [SQLSERVER\Instance].EnterpriseVaultDirectory.dbo.Archive A,
       [SQLSERVER\Instance].EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       [SQLSERVER\Instance].EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc
WHERE  S.VaultIdentity = V.VaultIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointID = R.VaultEntryID
  AND  V.VaultID = R2.VaultEntryID
  AND  R.RootIdentity = A.RootIdentity
  AND  R2.RootIdentity = AF.RootIdentity
  AND  rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
  AND  A.ArchiveName = 'Amy Miller'
GROUP BY A.ArchiveName, CAST(AF.FolderPath AS NVARCHAR(MAX)),AF.FolderName, rc.RetentionCategoryName
 

jpergola329's picture

tony,

would i need to change the archive name for each user i want to check?

thanks

im using ev 8 sp3 sql for the databases.

 

 

 

AndrewB's picture

the way it is now, yes. or you could run it without the "AND A.ArchiveName =" and it would run against all the archives. you could also substitute an IN instead of the = and give it a few archive names...

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner | www.trace3.com

jpergola329's picture

andrewb what would that look like

thanks

 

TonySterling's picture

It would look like this:

AND  A.ArchiveName IN ( 'Your ArchiveName', 'Your ArchiveName2','Your ArchiveName3')

So the complete query would be:

SELECT A.ArchiveName
       ,CAST(AF.FolderPath AS NVARCHAR(MAX))
       ,AF.FolderName
       ,COUNT(S.IdTransaction) "Number of Archived Items"
       ,rc.RetentionCategoryName AS "Retention Category"
      -- ,SUM(S.ItemSize) "Total Size (KB)"
FROM   Saveset S,
       ArchivePoint Ap,
       Vault V,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Root R2,
       EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc
WHERE  S.VaultIdentity = V.VaultIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointID = R.VaultEntryID
  AND  V.VaultID = R2.VaultEntryID
  AND  R.RootIdentity = A.RootIdentity
  AND  R2.RootIdentity = AF.RootIdentity
  AND  rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
  AND  A.ArchiveName IN ( 'Your ArchiveName', 'Your ArchiveName2','Your ArchiveName3')
GROUP BY A.ArchiveName, CAST(AF.FolderPath AS NVARCHAR(MAX)),AF.FolderName, rc.RetentionCategoryName

SOLUTION
jpergola329's picture

THANK YOU!!!!

thank you very much to everyone who answered...

i got what i needed.

these fourms are great.

 

jpergola329's picture

hey,

would this work with ev6?

thanks

 

Rob.Wilcox's picture

Doubtful - SQL Schema is completely different.

What is it you are trying to do?  Suggest a new forum post too :)