Please find here the new SQL query to get every archive from each VS DB:
(You can tweak it a little to add the type to differeciate between PF/FSA/mailbox ....)
######## START ########
Declare @variable table
(
ID int IDENTITY(1,1) PRIMARY KEY,
vsDB nvarchar(32)
)
create table ##list
(
archivename varchar(max),
creation date,
modified date,
Newest_Sent_Received_Item date,
Newest_Archived_Item date
)
insert into @variable
select DatabaseDSN from EnterpriseVaultDirectory.dbo.VaultStoreEntry
Declare @db nvarchar(32),
@Query NVARCHAR(MAX)
Declare @i int
select @i = min(ID) from @variable
Declare @max int
select @max = max(ID) from @variable
While @i <= @max
begin
select @db=(select vsDB from @variable where ID=@i)
--
set @Query =N'insert into ##list
SELECT A.ArchiveName "Archive Name",
Cast(AP.CreatedDate as date) "Archive Created",
Cast(AP.ModifiedDate as date) "Archived Last Modified",
Cast(MAX(S.IdDateTime) as date) "Newest Sent/Received Item",
Cast(MAX(S.ArchivedDate) as date) "Newest Archived Item"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
' + @db + '.dbo.ArchivePoint AP,
' + @db + '.dbo.Saveset S
WHERE A.RootIdentity = R.RootIdentity
AND R.VaultEntryId = AP.ArchivePointId
AND AP.ArchivePointIdentity = S.ArchivePointIdentity
GROUP BY A.ArchiveName, AP.CreatedDate, AP.ModifiedDate'
EXEC sp_executesql @Query
--
set @i=@i + 1
end
select * from ##list
drop table ##list
--there's no need to drop the table if you want to keep it. But drop it before running this query again.
######## END ########
Regards,
Vinoth Siva
