Video Screencast Help
Search Video Help Close Back
to help

Get all List of all Archives

Created: 13 Dec 2009
Wayne Humphrey's picture
Wayne Humphrey Trusted Advisor Partner
0 0 Votes
Login to vote
This SQL Script will get all List of all Archives and dispaly the following:
  • Mailbox Name
  • Exchange Server
  • Number of Items (Mailbox) 
  • Number of (Archive)
  • Mailbox Size (MB) 
  • Archive Size (MB)
  • Total Size (MB) 
  • Archive Created
  • Archive Updated
SELECT 
LEFT(MbxDisplayName,20) AS 'Mailbox', 
ExchangeComputer AS 'Exchange Server',
MbxItemCount AS '#Items (Mailbox)', 
VS1.ArchivedItems AS '#Items (Archive)',
MbxSize/1024 AS 'Mbx Size (MB)', 
VS1.ArchivedItemsSize/1024 AS  'Archive Size(MB)',
(mbxsize+VS1.ArchivedItemsSize)/1024 AS 'Total Size(MB)', 
VS1.CreatedDate AS 'Archive Created',
VS1.ModifiedDate AS 'Archive Updated', 
MbxExchangeState AS 'Exchange State'
 
FROM
EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry AS EME,
EnterpriseVaultDirectory.dbo.ExchangeServerEntry AS ESE,
EVVaultStore01..ArchivePoint AS VS1
 
WHERE 
EME.DefaultVaultID  = VS1.ArchivePointID AND
EME.ExchangeServerIdentity = ESE.ExchangeServerIdentity

If you have more than one Vault Store you will need to join this with a union

UNION
 
SELECT 
LEFT(MbxDisplayName,20) AS 'Mailbox',
ExchangeComputer AS 'Exchange Server',
MbxItemCount AS '#Items (Mailbox)',
VS2.ArchivedItems AS '#Items (Archive)',
MbxSize/1024 AS 'Mbx Size (MB)',
VS2.ArchivedItemsSize/1024 AS  'Archive Size(MB)',
(mbxsize+VS2.ArchivedItemsSize)/1024 AS 'Total Size(MB)',
VS2.CreatedDate AS 'Archive Created',
VS2.ModifiedDate AS 'Archive Updated', MbxExchangeState AS 'Exchange State'
 
FROM
EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry AS EME,
EnterpriseVaultDirectory.dbo.ExchangeServerEntry AS ESE,
EVVaultStore02..ArchivePoint AS VS2
 
WHERE
EME.DefaultVaultID = VS2.ArchivePointID AND
EME.MbxArchivingState = 1  AND
EME.ExchangeServerIdentity = ESE.ExchangeServerIdentity
Blog Entry Filed Under:
, , , , ,