How list Versioning Settings values are stored in Content Databases? - Sharepoint 2010

Good Morning Friends!

Hope you all are doing good! :)

Recently in our Project, during deployment, we came across few problems. Customer wanted to get rid off approval process for image and document libraries for publishing sites but they wanted to exclude "workspaces" sites. By workspaces sites means all sites which were coming under paths like "/sites/", "/projects/" & "/workspaces/".

So we prepared a powershell script for updating all those libraries inside publishing sites. But there was one glitch in the script, script only excluded publishing sites under "/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 had previous versioning settings logged to any file during script execution.

Now Customer wanted to have those old versioning settings back in place for "/sites/" and "/projects/" workspaces sites.

To compare the old versioning settings with new settings, we were thinking of couple of options.

One option was to compare old versioning settings from Old content database backup with Current content DB. But this was not very straight forward as there can be several content databases and it became very tedious job to find in which database the particular site and its lists belongs.

After finding out in which Content DB's the site and lists belongs, We would now need to fire a sql query to find out what is the value for different versioning settings from Content DB table.

But Where exactly values of different list Versioning settings are stored in Content Database?

and How its stored?

Versioning Settings values are stored in AllLists table in "tp_flags" column. List flags is an 8-byte unsigned integer bit mask that provides metadata about the list, which can have one or more flags set. List Flags identify an implementation-specific capability.

You can execute following SQL query specific to Content Database (where your list belongs) to identify whether moderation is enabled for the list or not. 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) & 0x0000000000000400 from allLists  --(where tp_WebID='< >' & tp_title = '< >')

0x0000000000000400

This list has moderation enabled, requiring an approval process when content is created or modified.

Similarly you can fetch values for different versioning settings like whether the versioning is enabled for list, whether minor version is enabled,  whether force checkout is enabled and so on.

There is list of all such List flags in this MS blog https://msdn.microsoft.com/en-us/library/hh646650(v=office.12).aspx

By comparing these db table flag values from Old and Current Content Databases, you will be able to compare values for different Versioning Settings.

In our case we were not having access to SQL database backups so we were not able to compare values from Content DB's.

Instead we moved to Second option by Restoring whole Sharepoint Farm backup on Test Enviornment and then Compared list versioning settings values from Powershell script.

Hope it will help somebody out there!

Happy Learning! :)