0

I want to read version history of document library.

and update few fields data of a particular version history in sqlserver table.

Any suggestions would be much appreciated.

Thanks in advance.

  • Microsoft does not support any changes from database , so it's heavily recommended to avoid any changes via database – Mohamed El-Qassas MVP Aug 31 '16 at 6:00
  • Thanks M.Qassas, I need to update to Sqlserver database (my own db) but not content database. I need to store version history data (few fields only) in my sql table. – raju Aug 31 '16 at 6:08
1

To get the versions history fields via SQL

  • Open SQl Management Studio > Connect to your SharePoint server.
  • Navigate to Content database like [WSS_Content].
  • Create a new query from > right click Design Query in Editor > Select [dbo].[AllDocVersions] table inner join [AllDocs] table on Doc ID, select your required fields to show.

the Query should look like with your required fields

SELECT        AllDocVersions.Id, AllDocVersions.UIVersion, AllDocs.DirName, AllDocs.LeafName
FROM            AllDocs INNER JOIN
                         AllDocVersions ON AllDocs.Id = AllDocVersions.Id

Output:

enter image description here

Note:

Microsoft does not support any changes from the database , so it's heavily recommended to avoid any changes via the database.

[update]

Below is a PowerShell code which can be used to export version history to Excel Ref

# ******* Variables Section ******************
#Define these variables
$WebURL="http://sharepoint.crescent.com/sites/Sales/"
$ListName ="Invoice"
$ReportFile = "D:\Invoice_VersionHistory.csv"
# *********************************************

#delete file if exists
If (Test-Path $ReportFile)
 {
 Remove-Item $ReportFile
 }

#Get the Web and List
$Web = Get-SPWeb $WebURL
$List = $web.Lists.TryGetList($ListName)

 #Check if list exists
 if($List -ne $null)
 {
  #Get all list items
  $ItemsColl = $List.Items

  #Write Report Header
  Add-Content -Path $ReportFile -Value "Item ID, Version Lable, Created by, Created at, Title"

  #Loop through each item
  foreach ($item in $ItemsColl)
  {
   #Iterate each version
      foreach($version in $item.Versions)
       {
    #Get the version content
    $VersionData = "$($item.id), $($version.VersionLabel), $($version.CreatedBy.User.DisplayName), $($version.Created), $($version['Title'])"
    #Write to report
    Add-Content -Path $ReportFile -Value $VersionData
   }
  }
 }
Write-Host "Version history has been exported successfully!"
| improve this answer | |
  • Can someone please suggest how can I read version history data using powershell or c#? – raju Aug 31 '16 at 8:11
  • I have updated my answer based on your new request, hope it helps you – Mohamed El-Qassas MVP Aug 31 '16 at 8:20
  • Thanks M.Qassas, it helped me, But I couldn't get other columns data. I added $($version['ColName']) after $($version['Title']). I'm getting the title column but not other column data. The other column has this value" ROM_STYLE" in csv file. – raju Sep 2 '16 at 8:05
  • it should retrive this column "$($item.id), $($version.VersionLabel), $($version.CreatedBy.User.DisplayName), $($version.Created), $($version['Title']) , so make sure that you already have versions for your items and versions enabled on the list , please don't hestitate to ask me for more clarafication , I am here to help you – Mohamed El-Qassas MVP Sep 2 '16 at 8:25
  • Hi @M.Qassas, Thanks for your support. I am able to retrieve columns "$($item.id), $($version.VersionLabel), $($version.CreatedBy.User.DisplayName), $($version.Created), $($version['Title']) data successfully. But I need to access columns other than "Title". I'm getting null values for other columns. – raju Sep 3 '16 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.