No ColName attribute available for field “_ModerationComments”

Question

When we try to get to the masterpage gallery there is just a message displayed saying "Process failed" (in portuguese "Falha de Processamento"), also if we try to change site masterpage through the SharePoint UI we get "Something went wrong.". At ULS Viewer it shows this error: No ColName attribute available for field "_ModerationComments".

I've saw a few people on the internet that faced a similar problem with other columns, but in custom lists/libraries, most of them solved by deleting and redoing it. As we are talking about the default masterpage gallery, obviously I can't do this to solve the problem...

We already tried to:

• Run the SharePoint Wizard
• Upgrade-SPContentDatabase
• Installed march/2016 CU
• Backup/Restore via database attach/detach
• New web application create, restore of .bak of the site in that new web app

All of those trials failed and we are still getting the same error. I'm guessing that this site collection for some reason has dropped this column and now this difference (betweeen list schema and DB columns) is crashing the masterpage gallery. We already opened a support call with Microsoft which after 2 months of analisis told us that THERE IS NO SOLUTION for this problem, also that didnt faced it ever.

At the moment I'm trying two new approaches:

• Export-SPWeb/Import-SPWeb : which I hope is not caring the database column, schema etc (never used this cmdlet thats why I dont really know what it does behind scenes)
• Rebuild the entire site collection and subwebs using powershell, and copy and paste all the content and permissions, which is in my opinion is the only possible way to "solve it", but painfull assuming some risks (like losing item versions) and the amount of work to do and check all of the sites (about 30 webs, around 5GB)

Hoping someone had solved this problem by himself, Im here asking you guys, Is there another way to solve this without copy/paste to another health site collection?

Answer 1

According to MSDN about ColName

Optional Text. An internal attribute that defines the mapping of the field to the physical storage name for this field. This must be a valid name in the underlying database and must be identical to the name used in the database table. If not specified, the server generates a column name that does not collide with any existing column names and that contains only characters that are allowed by Microsoft SQL Server.

Because we're talking about such a widely available library, I wouldn't be surprised if the database field was "ntext1".

The (apparently obvious) attempt would be to try and enforce this attribute into the existing list column, as per the following script:

clear-host

$web = get-spweb http://yourwebsite
$list = $web.Lists["Master Page Gallery"]
$fields = $list.Fields
$field = $fields.GetFieldByInternalName("_ModerationComments")
$field.SchemaXml
write-host ""
$schema = [xml]$field.SchemaXml

#$schema.Field.RemoveAttribute("ColName")
$schema.Field.SetAttribute("ColName", "ntext1")

#update schema with missing attribute
$field.SchemaXml = $schema.PSBase.OuterXml
$field.Update()
$field.SchemaXml

Yet another alternative is to try SPContentDatabase.Repair

$Db = Get-SPContentDatabase -WebApplication "http://SharePointApp/" 
$Db.Repair($false) #Repair false will list the objects
###$Db.Repair($true) #Changed to true to repair content Database

Needless to say, use all this at your own risk.

Make tests preferably on copies of the database.

Answer 2

So, after all the trials before redoing the site all over by hand, at last the Export/Import SPWeb worked out!

First I exported the broken site collection including all versions and user security:

Export-SPWeb "BROKEN_SITECOLLECTION_URL" -Path C:\SP\Backup\backup.cmp -IncludeVersions All -IncludeUserSecurity -NoLogFile -Compressionsize 100000000

Then I created a new SPSite, this new site collection MUST be created with the same template as the exported one otherwise the import will not work! Which in my case was "SPS#0":

New-SPSite HEALTH_SITECOLLECTION_URL -OwnerAlias "DOMAIN\OWNER_USER" -SecondaryOwnerAlias "DOMAIN\SEC_OWNER_USER" -Name "SITE_TITLE" -Template "SPS#0"

Then I just did the Import-SPWeb:

Import-SPWeb "HEALTH_SITECOLLECTION_URL" -Path C:\SP\Backup\backup.cmp -ActivateSolutions -IncludeUserSecurity -IncludeUserCustomAction All

All the UserSecurity came as well with the IncludeUserSecurity, just not the site collection administrators that I needed to put them back. I also needed to activate/deactivate some features by hand.