SET NOCOUNT ON 

DECLARE @ErrorTag NVARCHAR(20) 
SET			@ErrorTag = '###ERROR###'

DECLARE @WarningTag NVARCHAR(20) 
SET			@WarningTag = '###WARNING###'

DECLARE @AdviceTag NVARCHAR(20) 
SET			@AdviceTag = '###ADVICE###'

DECLARE @HeaderTag NVARCHAR(20) 
SET			@HeaderTag = '###HEADER###'

DECLARE @DataTag NVARCHAR(20) 
SET			@DataTag = '###DATA###'

DECLARE @DescTag NVARCHAR(20) 
SET	@DescTag = '###DESCRIPTION###'

DECLARE @colSeperator NVARCHAR(20) 
SET			@colSeperator = ':,:'

DECLARE @MANameCurFlag  INTEGER 
SET 		@MANameCurFlag  = 0 

DECLARE @MACount 		INTEGER 
SET			@MACount		= 0

DECLARE @errorCode  INTEGER 
SET			@errorCode	= 0

DECLARE @retCode  INTEGER 
SET			@retCode	= 0

DECLARE @HostId 	INTEGER 
DECLARE @MAName 	NVARCHAR(510) 
DECLARE @SIDBStoreId	INTEGER 
DECLARE @PolicyName NVARCHAR(510) 
DECLARE @CopyName 	NVARCHAR(510) 

DECLARE @MAWithDeprecatedDDBVersion TABLE
(
		HostId	INTEGER,
		MAName		NVARCHAR(510),
		SIDBStoreId INTEGER
		PRIMARY KEY (SIDBStoreId)
)

PRINT @DescTag + ' Check for MediaAgents with deprecated Deduplication databases (DDBs) version'

INSERT INTO @MAWithDeprecatedDDBVersion
SELECT DISTINCT AC.Id, AC.name, SS.SIDBStoreId
FROM APP_Client AC WITH (NOLOCK), IdxSIDBStore SS WITH (NOLOCK), archGroupCopy AGC WITH (NOLOCK)
WHERE AC.id = SS.ClientId
AND	  SS.SIDBStoreId > 0
AND	  SS.Status != 1		-- IDX_SIDBSTORE_STATUS_CORRUPTED  (Non corrupted stores)
AND	  SS.SealedTime = 0
AND 	SS.firstBackupTime > 0
AND	  SS.Version < 2		--Created in 8.0 and earlier
AND		SS.SIDBStoreId = AGC.SIDBStoreId

SELECT @errorCode	= @@ERROR, @MACount = @@ROWCOUNT
IF	@errorCode != 0 GOTO CX_EXIT 

--If no Media Agents found with deprecated DDB version
IF @MACount = 0 
BEGIN 
	SET @retCode = 0
	PRINT 'No MediaAgents have been found with deprecated DDB version.'
	GOTO CX_EXIT
END 

--If Media Agent(s) found with deprecated DDB version then just give warning in the Pre-Upgrade check
SET @retCode = 1
PRINT @WarningTag + ' Found one or more MediaAgents that have active Deduplication databases (DDBs) from a deprecated version.'

--Print Header
PRINT @HeaderTag + ' Media Agents:,:Policy Name:,:Copy Name'
PRINT '-----------------------------------------------------'
	
DECLARE	MANameCur CURSOR STATIC FOR 
SELECT HostId, MAName, AG.Name, AGC.Name
FROM @MAWithDeprecatedDDBVersion M, archGroupCopy AGC, archGroup AG
WHERE	M.SIDBStoreId = AGC.SIDBStoreId
		AND (AGC.DedupeFlags & 134217728) = 0 -- CVA_USE_GLOBAL_DEDUP_STORE_FLAG, Skip the copies associated with GDSP
		AND AGC.archGroupId = AG.id

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

SET @MANameCurFlag = 1 
OPEN MANameCur 

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

FETCH NEXT 
FROM  MANameCur 
INTO  @HostId, @MAName, @PolicyName, @CopyName 

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

WHILE @@FETCH_STATUS = 0 
BEGIN 
	PRINT @DataTag + ' ' + @MAName + ':,:' + @PolicyName + ':,:' + @CopyName

	FETCH NEXT 
	FROM  MANameCur 
	INTO  @HostId, @MAName, @PolicyName, @CopyName 

	SET	@errorCode	=	@@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END 

--Print Advice
PRINT @AdviceTag + ' If you upgrade MediaAgents that host DDBs from deprecated versions,'
								 + ' the DDBs should be either sealed or converted to a version 9 DDB to prevent a new baseline for backup data in the latest software version.'
								 + ' Refer to the Documentation for the steps to convert the DDBs.'

CX_EXIT: 

IF @MANameCurFlag = 1 
BEGIN 
	CLOSE	    MANameCur 
	DEALLOCATE	MANameCur 
END

IF @errorCode != 0
	SELECT 2
ELSE
	SELECT @retCode

RETURN