SQL script to remove VMware VM protection references from DPMDB
Important
Only use this SQL script to resolve the issue when you upgrade from DPM 2012 R2 to DPM 2016 with VMware VM protection.
SET XACT_ABORT ON
BEGIN TRANSACTION
IF EXISTS( SELECT Ds.DataSourceID FROM tbl_IM_DataSource Ds JOIN tbl_PRM_LogicalReplica Lr ON Ds.DataSourceId = Lr.DataSourceId WHERE Ds.AppId = '18BEE66C-826F-4499-A663-9805C8688AD3')
PRINT 'VMware DataSource in Active/Inactive protected state'
ELSE IF EXISTS ( SELECT DataSourceID FROM tbl_IM_DataSource WHERE CloudProtectionStatus!=0 AND AppId='18BEE66C-826F-4499-A663-9805C8688AD3')
PRINT 'VMware DataSource in Cloud are Active/Inactive protected state'
ELSE
PRINT 'All VMware datasource protections are removed'
SELECT serverID INTO #serverIdTable FROM dbo.tbl_AM_Server WHERE NOT ProxyServerId IS NULL
DECLARE @serverId nvarchar(100)
WHILE exists (SELECT * FROM #serverIdTable)
BEGIN
SELECT @serverId = (SELECT TOP 1 ServerId FROM #serverIdTable)
PRINT @serverId
--Remove Entries in different tables that belong to @serverId
DELETE FROM dbo.tbl_RM_RecoveryTrail WHERE ServerId=@serverId
DELETE FROM dbo.tbl_AM_InstalledAgent WHERE ServerId=@serverId
DELETE FROM dbo.tbl_AM_ServerTimeZone WHERE ServerId=@serverId
DELETE FROM dbo.tbl_IM_ProtectedObject WHERE ServerId=@serverId
DELETE FROM dbo.tbl_AM_ServerProperties WHERE ServerId=@serverId
DELETE FROM dbo.tbl_IM_PendingVMwareCustomAttr WHERE ServerId=@serverId
DELETE FROM dbo.tbl_PRM_DatasourceBitmapVolumeMap WHERE ServerID=@serverId
--To this point it is fine
DELETE #serverIdTable WHERE serverId=@serverId
END
DROP TABLE #serverIdTable
SELECT serverID INTO #serverIdTable2 FROM dbo.tbl_AM_Server WHERE NOT ProxyServerId IS NULL
DECLARE @serverId2 nvarchar(100)
WHILE exists (SELECT * FROM #serverIdTable2)
BEGIN
SELECT @serverId2 = (SELECT TOP 1 ServerId FROM #serverIdTable2)
PRINT @serverId2
SELECT DatasourceID INTO #datasourceIdTable FROM dbo.tbl_IM_DataSource WHERE ServerId=@serverId2
DECLARE @datasourceId nvarchar(100)
WHILE exists ( SELECT * FROM #datasourceIdTable )
BEGIN
SELECT @datasourceId = ( SELECT TOP 1 DatasourceId FROM #datasourceIdTable)
DELETE FROM dbo.tbl_IM_DatasourceCapability WHERE DatasourceId=@datasourceId
DELETE FROM dbo.tbl_IM_VMWareProperties WHERE DatasourceId=@datasourceId
DELETE FROM dbo.tbl_RM_ShadowCopyTrail WHERE DatasourceId=@datasourceId
DELETE #datasourceIdTable WHERE DataSourceId=@datasourceId
END
DROP TABLE #datasourceIdTable
DELETE FROM dbo.tbl_RM_ReplicaTrail WHERE ServerId=@serverId2
DELETE FROM dbo.tbl_IM_DataSource WHERE ServerID=@serverId2
DELETE #serverIdTable2 WHERE serverId=@serverId2
END
DROP TABLE #serverIdTable2
SELECT serverID INTO #serverIdTable3 FROM dbo.tbl_AM_Server WHERE NOT ProxyServerId IS NULL
DECLARE @serverId3 nvarchar(100)
WHILE exists (SELECT * FROM #serverIdTable3)
BEGIN
SELECT @serverId3 = (SELECT TOP 1 ServerId FROM #serverIdTable3)
PRINT @serverId3
--find Job definitions
SELECT JobDefinitionID INTO #jobDefinitionIdTable FROM dbo.tbl_JM_JobDefinition WHERE ServerId=@serverId3
DECLARE @jobDefinitionId nvarchar(100)
WHILE exists ( SELECT * FROM #jobDefinitionIdTable)
BEGIN
SELECT @jobDefinitionId = ( SELECT TOP 1 JobDefinitionId FROM #jobDefinitionIdTable)
--Delete from JobTrail but before that we need to delete from TaskTrail.
--Find jobId associated with job definition and remove them
SELECT JobId INTO #jobIdTable FROM dbo.tbl_JM_JobTrail WHERE JobDefinitionId=@jobDefinitionId
DECLARE @jobId nvarchar(100)
WHILE exists ( SELECT * FROM #jobIdTable)
BEGIN
SELECT @jobId = ( SELECT TOP 1 JobId FROM #jobIdTable)
DELETE FROM dbo.tbl_TE_TaskTrail WHERE JobID=@jobId
DELETE #jobIdTable WHERE JobId=@jobId
END
DROP TABLE #jobIdTable
--Remove Task Definition and schedule associated with JobDefinitionId
DELETE FROM dbo.tbl_JM_TaskDefinition WHERE JobDefinitionId=@jobDefinitionId
DELETE FROM dbo.tbl_JM_JobTrail WHERE JobDefinitionId=@jobDefinitionId
DELETE FROM dbo.tbl_SCH_ScheduleDefinition WHERE JobDefinitionId=@jobDefinitionId
DELETE #jobDefinitionIdTable WHERE JobDefinitionId=@jobDefinitionId
END
DROP TABLE #jobDefinitionIdTable
DELETE FROM dbo.tbl_JM_JobDefinition WHERE ServerId=@serverId3
DELETE FROM dbo.tbl_AM_Server where ServerId=@serverId3
DELETE #serverIdTable3 WHERE serverId=@serverId3
END
DROP TABLE #serverIdTable3
SELECT DatasourceID INTO #datasourceIdTable2 FROM dbo.tbl_IM_DataSource WHERE AppId='18BEE66C-826F-4499-A663-9805C8688AD3'
DECLARE @datasourceId2 nvarchar(100)
WHILE exists ( SELECT * FROM #datasourceIdTable2 )
BEGIN
SELECT @datasourceId2 = ( SELECT TOP 1 DatasourceId FROM #datasourceIdTable2)
DELETE FROM dbo.tbl_IM_DatasourceCapability WHERE DatasourceId=@datasourceId2
DELETE FROM dbo.tbl_IM_VMWareProperties WHERE DatasourceId=@datasourceId2
DELETE FROM dbo.tbl_IM_ProtectedObject WHERE DatasourceId=@datasourceId2
DELETE #datasourceIdTable2 WHERE DataSourceId=@datasourceId2
END
DROP TABLE #datasourceIdTable2
Delete from tbl_IM_DataSource where AppId='18BEE66C-826F-4499-A663-9805C8688AD3'
COMMIT;
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for