Upgrading to 5.3.0 or 5.3.1 fails with the following error:
Database installation failed. Reason: 'DF_Repository_DedupEnabled' is not a constraint.
Could not drop constraint. See previous errors.
Error in c:\Program Files (x86)\Quest Software\Service\vRangerProDBUpgradeScripts\vRangerPro.5.3.0.sql
Column constraints incorrectly defined.
1. Run fix-rollback.5.2.sql script using Management Studio or sqlcmd.
Script will return 3 lines with constraint names. Numbers at the end of each name can be different. These constraints are deleted and new correct ones are defined.
DF__Repositor__Dedup__2F10CBD2
DF__Repositor__Compr__3004F00B
DF__Repositor__Block__30F91444
2. Run Ranger upgrade package
Script Below
5.2.rollback.sql fix
------------------------------------------------------------------
-- Script 5.2.rollback.sql had an error. Upgrading to 5.3.0 or 5.3.1 will fail.
-- Database installation failed. Reason: 'DF_Repository_DedupEnabled' is not a constraint.
-- Could not drop constraint. See previous errors.
-- Error in c:\Program Files (x86)\Quest Software\Service\vRangerProDBUpgradeScripts\vRangerPro.5.3.0.sql
-- run following script. Then upgrade another time.
USE vRangerPro
--remove DEFAULT constraint and columns
DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)
SET @defname =
(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = 'Repository'
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id('dbo.Repository') AND
name = 'DedupEnabled'))
print @defname
SET @cmd = 'ALTER TABLE [Repository] DROP CONSTRAINT ' + @defname
EXEC(@cmd)
ALTER TABLE [Repository] DROP column DedupEnabled
SET @defname =
(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = 'Repository'
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id('dbo.Repository') AND
name = 'Compressed'))
print @defname
SET @cmd = 'ALTER TABLE [Repository] DROP CONSTRAINT ' + @defname
EXEC(@cmd)
ALTER TABLE [Repository] DROP column Compressed
SET @defname =
(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = 'Repository'
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id('dbo.Repository') AND
name = 'BlockSize'))
print @defname
SET @cmd = 'ALTER TABLE [Repository] DROP CONSTRAINT ' + @defname
EXEC(@cmd)
ALTER TABLE [Repository] DROP column BlockSize
-- add same 3 columns with correctly defined default constraints
ALTER TABLE Repository ADD DedupEnabled bit CONSTRAINT [DF_Repository_DedupEnabled] DEFAULT(0) NOT NULL
ALTER TABLE Repository ADD Compressed bit CONSTRAINT [DF_Repository_Compressed] DEFAULT (0) NOT NULL
ALTER TABLE Repository ADD BlockSize int CONSTRAINT [DF_Repository_BlockSize] DEFAULT (0) NOT NULL
