Restore content versions

After a long journey we managed to upgrade from Optimizely/Episerver CMS v11 to CMS v12. We had a lot of tests and many people involved. We went live with CMS v12 and everyone was happy. A couple of days went by and suddenly we got “that” email from one of the editors.

“Where is the old version of page X?” We want to see how page X looked like a couple of months ago.

Our problem is that we used to have the flag uiMaxVersions="0" (The maximum number of page versions that EPiServer will retain.) set in the CMS v11 and we saved all versions of content. (Default is that Optimizely/Episerver CMS store the last 20 versions for each content.)

In CMS11 we had this configuration in Web.config:

<configuration>
  ...
  <episerver>
  ...
    <applicationSettings ... uiMaxVersions="0" ... />  
  ..
  </episerver>
  ...
</configuration>

And we simple forgot to set that in the new CMS v12 upgrade. So quickly we added that configuration and deploy to production.

Appsettings.config {
  ...
  "EPiServer": {
    "CMS": {
      ...
      "Content": {
        ...
        "MaximumVersions": "0"
        ...
      }
    }
  }
  ...
}


So now we started to save all versions again. But the problem were still there. We were still missing all versions that was already removed from the database.

But we had of course some database backups from the old CMS v11 database. So, we started to work on a solution to restore the versions.

After a lot of testing, we came up with a TSQL script that could read from CMS v11 database and add missing information into the CMS v12 database. Both these databases were placed in the same SQL Sever so that we could connect to the SQL Server with SQL Server Management Studio with SA user.

This is the magic script:

SET NOCOUNT ON
BEGIN TRANSACTION
 
--### Create temp tables where we store some diff info.
IF OBJECT_ID('tempdb..#Content') IS NOT NULL DROP TABLE #Content
GO
IF OBJECT_ID('tempdb..#WorkContent') IS NOT NULL DROP TABLE #WorkContent
GO
IF OBJECT_ID('tempdb..#WorkContentProperty') IS NOT NULL DROP TABLE #WorkContentProperty
GO
 
DECLARE @ContentId int
DECLARE @WorkContentId int
DECLARE @FoundNumber int
 
CREATE TABLE #Content (ContentId INT)
CREATE TABLE #WorkContent (WorkContentId INT, ContentId INT)
CREATE TABLE #WorkContentProperty (WorkContentPropertyId INT,WorkContentId INT, ContentId INT)
 
--### Get all content items from the new database. We want to go through all of these and check for content versions.
INSERT INTO #Content (ContentId) (SELECT pkID AS ContentId FROM [cms12_database].[dbo].[tblContent])
 
--### Make sure that we can create pkID with same ID as before on [tblWorkContent]
SET IDENTITY_INSERT [cms12_database].[dbo].[tblWorkContent] ON
 
--### The we loop through all content items with a cursor
DECLARE db_cursor CURSOR FOR SELECT ContentId FROM #Content
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @ContentId  
WHILE @@FETCH_STATUS = 0  
BEGIN  
    --### Reset the found number variable
    SET @FoundNumber = 0
 
    --### Create a list of all WorkContent (content versions) that exist in old database that is missing in the new database.
    INSERT INTO #WorkContent (WorkContentId, ContentId) (SELECT pkID AS WorkContentId, @ContentId FROM [cms11_database].[dbo].[tblWorkContent] WHERE fkContentId = @ContentId
    --AND pkID NOT IN (274611, 274612) --### You can use this extra filtration if you realize that there is content created on both databases after go live with cms12 version.
    EXCEPT
    SELECT pkID AS WorkContentId, @ContentId FROM [cms12_database].[dbo].[tblWorkContent] WHERE fkContentId = @ContentId)
    --AND pkID NOT IN (274611, 274612) --### You can use this extra filtration if you realize that there is content created on both databases after go live with cms12 version.
 
    --### Check if there is any missing content version
    SELECT @FoundNumber = COUNT(WorkContentId) FROM #WorkContent WHERE ContentId = @ContentId
    IF (@FoundNumber > 0)
    BEGIN
        PRINT 'Fix WorkContent for ContentId=' + CONVERT(nvarchar(10), @ContentId)
 
        --### Copy over all WorkContent (content versions) that exist in old database that is missing in the new database.
        INSERT INTO [cms12_database].[dbo].[tblWorkContent] ([pkID],[fkContentID],[fkMasterVersionID],[ContentLinkGUID],[fkFrameID],[ArchiveContentGUID],[ChangedByName],[NewStatusByName],[Name],[URLSegment],[LinkURL],[BlobUri],
        [ThumbnailUri],[ExternalURL],[VisibleInMenu],[LinkType],[Created],[Saved],[StartPublish],[StopPublish],[ChildOrderRule],[PeerOrder],[ChangedOnPublish],[RejectComment],[fkLanguageBranchID],[CommonDraft],[Status],[DelayPublishUntil])
        SELECT [pkID],[fkContentID],[fkMasterVersionID],[ContentLinkGUID],[fkFrameID],[ArchiveContentGUID],[ChangedByName],[NewStatusByName],[Name],[URLSegment],[LinkURL],[BlobUri],
        [ThumbnailUri],[ExternalURL],[VisibleInMenu],[LinkType],[Created],[Saved],[StartPublish],[StopPublish],[ChildOrderRule],[PeerOrder],[ChangedOnPublish],[RejectComment],[fkLanguageBranchID],[CommonDraft],[Status],[DelayPublishUntil]
        FROM [cms11_database].[dbo].[tblWorkContent] WHERE pkID IN (SELECT WorkContentId FROM #WorkContent WHERE ContentId = @ContentId)
    END
 
      FETCH NEXT FROM db_cursor INTO @ContentId
END
CLOSE db_cursor  
DEALLOCATE db_cursor
 
--### Turn on auto increment on table again
SET IDENTITY_INSERT [cms12_database].[dbo].[tblWorkContent] OFF
 
--###  Make sure that we can create pkID with same ID as before on [tblWorkContentProperty]
SET IDENTITY_INSERT [cms12_database].[dbo].[tblWorkContentProperty] ON
 
--### For each missing WorkContent (content version)
DECLARE db_cursor CURSOR FOR SELECT WorkContentId, ContentId FROM #WorkContent
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @WorkContentId, @ContentId  
WHILE @@FETCH_STATUS = 0  
BEGIN  
 
    PRINT 'Fix WorkContentProperties for WorkContentId=' + CONVERT(nvarchar(10), @WorkContentId)
    --### Create a list of all content properties for each work content row.
    INSERT INTO #WorkContentProperty (WorkContentPropertyId, WorkContentId, ContentId) (SELECT pkID AS WorkContentPropertyId, fkWorkContentID AS WorkContentId, @ContentId FROM [cms11_database].[dbo].[tblWorkContentProperty] WHERE fkWorkContentId = @WorkContentId
    EXCEPT
    SELECT pkID AS WorkContentPropertyId, fkWorkContentID AS WorkContentId, @ContentId FROM [cms12_database].[dbo].[tblWorkContentProperty] WHERE fkWorkContentId = @WorkContentId)
 
    --### Copy over all WorkContentProperty that does not exist in the new database. (- ProprtyDefinition that is removed/diff against old database)
    INSERT INTO [cms12_database].[dbo].[tblWorkContentProperty] ([pkID],[fkPropertyDefinitionID],[fkWorkContentID],[ScopeName],[guid],[Boolean],[Number],[FloatNumber],[ContentType],[ContentLink],[Date],[String],[LongString],[LinkGuid])
    SELECT [pkID],[fkPropertyDefinitionID],[fkWorkContentID],[ScopeName],[guid],[Boolean],[Number],[FloatNumber],[ContentType],[ContentLink],[Date],[String],[LongString],[LinkGuid]
    FROM [cms11_database].[dbo].[tblWorkContentProperty] WHERE pkID IN (SELECT WorkContentPropertyId FROM #WorkContentProperty WHERE WorkContentId = @WorkContentId) AND fkPropertyDefinitionID IN (SELECT pkID AS PropertyDefinitionID FROM [cms12_database].[dbo].[tblPropertyDefinition])
 
      FETCH NEXT FROM db_cursor INTO @WorkContentId, @ContentId
END
CLOSE db_cursor  
DEALLOCATE db_cursor
 
--### Turn on auto increment on table again
SET IDENTITY_INSERT [cms12_database].[dbo].[tblWorkContentProperty] OFF
 
COMMIT TRANSACTION


So now we have copied over all WorkContent + WorkContentProperty data that was removed from the cms12_database during the time when config was wrong.

Before we run the script:

And after we run the script we could see that all versions now is back:

I hope that this help someone out there!

NOTE: We will not take any responsibility of any data loos etc. You should do this on test databases first and make sure that it does not break any functionality in your application. Just because this worked for us in this case you could have some specific exception that this script does not handle. Handle with care!