Restore items from Enterprise Vault

[StevenHill]

We have been asked to provide details on the size\amount of data that would be restored from Enterprise Vault if  we took everyone's vault and recovered data newer that 1 year (i.e. 1 year old up to todays date). Does anyone have a method of reporting this? We are currently running Enterprise Vault 8.0 SP2 with no Discovery Accellerator.

[Liam_Finn1]

you could query the vault store databases in the Saveset table to see which items have an idDateTime less than X in the and have it return all the fields then enumerate the column with the item size

[Liam_Finn1]

Maybe a query like this will help

Select * from dbo.Saveset
Where IdDateTime < '1/1/2008 00:00:00'

[Liam_Finn1]

Just an additional note the IdDateTime is the last modified date and time of the doc. If you want o go with Archive Date you replace IdDate Time with ArchivedDate and the date sequence is different '2008-01-01 00:00:00.000'

[TonySterling]

How about something like this:

see below...edited script

[Liam_Finn1]

If you want to run Either my or Tony's scripts against more than one database within an SQL server you need to invoke an SQL Storeed Procedure sp_MSForEachdb is a hidden SP in SQL 2005

The databases listed after NOT IN are the databases you dont want to include because they dont contain the data you need. If the SP is run against a database that does not have the data it will fail so make sure you custiomize this to suit your environment

EXEC sp_MSForEachdb 'IF ''?'' NOT IN (''msdb'', ''tempdb'', ''model'', ''EnterpriseVaultDirectory'', ''EnterpriseVaultAudit'')

Select * from dbo.Saveset
Where IdDateTime < '1/1/2008 00:00:00'

Or in the case of Tony's  Script

EXEC sp_MSForEachdb 'IF ''?'' NOT IN (''msdb'', ''tempdb'', ''model'', ''EnterpriseVaultDirectory'', ''EnterpriseVaultAudit'')
SELECT ex.MbxDisplayName,
a.archiveditems as Count, (a.archiveditemsSize/1024) as 'Size'
FROM Saveset s
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
WHERE s.IdDateTime > GetDate()-365
GROUP BY ex.MbxDisplayName, a.archiveditems, a.archiveditemssize'

[TonySterling · Accepted solution]

just run this against the vaultstore database:

--This gives the archive name and size of archive in mb
--Runs against the VaultStore Database
SELECT ex.MbxDisplayName,
Count(s.ArchivePointIdentity) as Count,  SUM (s.ItemSize)/1024 as 'Size'
FROM Saveset s
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
WHERE s.IdDateTime > GetDate()-365
GROUP BY ex.MbxDisplayName, s.ArchivePointIdentity

[Liam_Finn1]

Nice script :)

[StevenHill]

Can't thank you guys enough!

[TonySterling]

Thanks Liam!  I had to make some changes so it would show the correct values.  :)