Vault SQL DB FK_ContentSourceIndexStatus_Entity. Where?!

[hans.martin.haga]

I have a severely crashed Vault/SQL database, where I could save some time rebuilding a new Vault by exporting the configuration. Unfortunately, the Service Pack 1 for Vault 2013 was not installed - so the Export Bug is present. Unfortunately, again, it is not possible to migrate the database after SP1 - because of incorrect entries in the database.

I need to delete the foreign key FK_ContentSourceIndexStatus_Entity, but I can't find it anywhere in the databases!

Where is it???

[hans.martin.haga]

----------- Grindstone to Grindstone SP1 migration -----------
--

--
--Potential Replication conflicts - Content Source Index Status (ContentSourceId, ContentSourceIndexStatus) moved out of FileMaster into new Table [ContentSourceIndexStatus]
--To avoid potential conflict where a Property File Re-Index can be updating ContentSourceId, ContentSourceIndexStatus on table [FileMaster] on one workgroup
--while the file is curently Checked Out on another workgroup - the result being that the FileMaster update by the Re-Index can potentially "remove" Checkout info on the other workgroup


--First, create new ContentSourceIndexStatus table
CREATE TABLE [dbo].[ContentSourceIndexStatus] (
	[EntityId] [bigint] NOT NULL ,
    [ContentSourceId]         [bigint] NULL,
	[ContentSourceIndexStatus] [bit] NULL,
	
	CONSTRAINT [PK_ContentSourceIndexStatus] PRIMARY KEY CLUSTERED	( [EntityId] ASC	) ON [PRIMARY],
	CONSTRAINT [FK_ContentSourceIndexStatus_Entity] FOREIGN KEY ([EntityId]) REFERENCES [dbo].[Entity] ([EntityId]) ON DELETE CASCADE NOT FOR REPLICATION,

) ON [PRIMARY]

--Move current  ContentSourceId, ContentSourceIndexStatus values from FileMaster using the tip iteration of the master
--Like ContentSourceProperties we store the tip iteration (rather than the master Id) to avoid conflicts with Replication.
INSERT INTO [dbo].[ContentSourceIndexStatus] (EntityId, ContentSourceId, ContentSourceIndexStatus)
SELECT m.TipIterationId, fm.ContentSourceId, fm.ContentSourceIndexStatus FROM dbo.FileMaster fm
INNER JOIN dbo.Master m ON m.MasterId = fm.FileMasterId WHERE m.TipIterationId IS NOT NULL

--We also need to create an entry where TipCommitted <> TipIteartion (e.g. for any checked out files)
--Like ContentSourceProperties we store both the TipCommitted and TipIteartion version until file is Checked In or Undone
INSERT INTO [dbo].[ContentSourceIndexStatus] (EntityId, ContentSourceId, ContentSourceIndexStatus)
SELECT m.TipCommittedIterationId, fm.ContentSourceId, fm.ContentSourceIndexStatus FROM dbo.FileMaster fm
INNER JOIN dbo.Master m ON m.MasterId = fm.FileMasterId WHERE (m.TipIterationId IS NOT NULL AND m.TipCommittedIterationId IS NOT NULL) AND m.TipIterationId <> m.TipCommittedIterationId

go

 Aha, the table and the foreignkey is created by the upgrade script.

Is it possible to edit the .SQL files used by the SP1 upgrade script? I do not need this database to be in a consistent state, I only need to be able to apply SP1 to be able to export the configuration (Categories, Lifecycles etc).

[minkd]

Do not modify the database!!  Once you do that we cannot support you, and will very likely create more problems for yourself.

The right thing to do in a situation like this is to contact product support.

-Dave

[hans.martin.haga]

My intention is only to edit a database that will never be used again. It will only be used to export the vault configuration.

The database is broken, as parts of it could not be recovered after a RAID failure (2 drives of 5 failed). I managed to run a SQL repair with data loss, but Service Pack 1 will not install - probably because of incorrect db entries.

[minkd]

If all you care about is exporting the configuration, then why not restore your last good backup and export from there?

-Dave

[hans.martin.haga]

The big problem here is that the customer has not been running a proper backup...