Problem
In our SharePoint 2010 project, during deployment, we came across a few problems. The customer wanted to eliminate the approval process for image and document libraries for publishing sites but they wanted to exclude "workspaces" sites. Workspace sites means all sites under paths like "/sites/", "/projects/" and "/workspaces/".
So we prepared a PowerShell script for updating all those libraries in the publishing sites. But there was one glitch in the script, the script only excluded publishing sites under a "/workspaces/" path and applied settings on publishing sites under "/sites/" and "/projects/" paths whereas we wanted to exclude all 3 workspaces paths. And to add to this problem, we didn't even have previous versioning settings logged to any file during script execution.
Now the customer wanted to have those old versioning settings back instead for "/sites/" and "/projects/" workspaces sites.
Solution
To compare the old versioning settings with the new settings, we were thinking of a couple of options.
One option was to compare the old versioning settings from the old content database backup with the current content DB. But this was not very straight forward since there can be several content databases and it became a very tedious job to find in which database the specific site and its lists belong.
After determining in which Content DB's the site and lists belong, we would now need to fire a SQL query to determine the value for various versioning settings from the Content DB table.
But where exactly are the values of the list Versioning settings stored in the Content Database? How are they stored?
Versioning Setting values are stored in the AllLists table in the "tp_flags" column. List flags is an 8-byte unsigned integer bit mask that provides metadata about the list that can have one or more flags set. List Flags identify an implementation-specific capability.
You can execute the following SQL query specific to the Content Database (where your list belongs) to identify whether moderation is enabled for the list. If the value returned by SQL query is zero then moderation is not enabled and if other than zero then moderation is enabled.
Select cast(tp_flags as bigint) and 0x0000000000000400 from allLists --(where
| 0x0000000000000400 |
This list has moderation enabled, requiring an approval process when content is created or modified. |
tp_WebID='< >' and tp_title = '< >').
Similarly you can fetch values for various versioning settings, like whether the versioning is enabled for the list, whether minor version is enabled, whether force checkout is enabled and so on.
There is list of all such List flags in this Microsoft blog.
By comparing these db table flag values from Old and Current Content databases, you will be able to compare values for various Versioning Settings.
In our case we did not have access to SQL database backups so we were not able to compare values from Content DBs.
Instead we moved to the second option of restoring the entire SharePoint Farm backup to the Test Environment and then compared the list versioning settings values from a PowerShell script.
Important Note
Microsoft strongly recommends not to update anything in the SharePoint DB. This preceding article is just to provide you an idea of how to retrieve information from the content dbs only in the case of emergency situations.
I hope it will help somebody out there!