RSS

Extract documents from a SharePoint database

12 May

We had a situation come up where we had taken down a SharePoint server and of course all of the users said they had everything they needed from it until 3 weeks later when several documents were found “conveniently missing”. All we had left were the databases from the installation and I found a really good script to extract documents from your database.

General Warning

This script goes directly against a MOSS 2007 content database which is generally discouraged. Any code using the database directly will not be supported by MS Product Support Services. Instead, the SharePoint and WSS APIs are the way to go. If the database is modified directly rather than through the published SharePoint and WSS APIs Product Support Services cannot properly troubleshoot any unexpected issues. This script reads from the database so we’re safe from errant modifications. However, the data structure the script queries could change in a future service pack.

Here is the script we used and you can copy and paste this into Notepad and save it as ex “extractdoc.vbs”.

Dim contentDatabase
Dim leaf
Dim outputPath
server = “server name
contentDatabase = “database name
leaf = “filename
outputPath = “C:\Temp\filename

ExtractDoc server, contentDatabase, leaf, outputPath

Sub ExtractDoc(server, contentDatabase, leaf, outputPath)

  Dim conStr, selectStr

  conStr = “Provider=SQLOLEDB;data Source=” + server + “;Initial Catalog=” + contentDatabase + “;Trusted_Connection=yes”

  selectStr = “SELECT dbo.AllDocStreams.Content FROM dbo.AllDocs ”
  selectStr = selectStr + “INNER JOIN dbo.AllDocStreams ”
  selectStr = selectStr + ”  ON dbo.AllDocs.ID= dbo.AllDocStreams.ID ”
  selectStr = selectStr + ” AND dbo.AllDocs.Level = dbo.AllDocStreams.Level ”
  selectStr = selectStr + ” where LeafName='” + leaf +”‘ AND IsCurrentVersion=1″

  Set cn = CreateObject(“ADODB.Connection”)
  Set rs = CreateObject(“ADODB.Recordset”)
  cn.Open conStr
  Set rs = cn.Execute(selectStr)
  Set mstream = CreateObject(“ADODB.Stream”)
  mstream.Type = 1
  mstream.Open
  mstream.Write rs.Fields(“Content”).Value
  mstream.SaveToFile outputPath, 2
  rs.Close
  cn.Close
End Sub

This took some research to find but it worked like a charm. Simply replace the server name, database name and filename fields with the appropriate values and you’re good to go. The filenames unfortunately have to be exact or it will not find it. Also, be sure to include the extension with the filename.

Hope this helps!

Advertisements
 
2 Comments

Posted by on May 12, 2009 in SharePoint Tips

 

2 responses to “Extract documents from a SharePoint database

  1. David Bradford

    August 8, 2012 at 3:52 PM

    Hi Laura,
    Thanks so much for this post! I recently inherited a SarePoint application that uses InfoPath forms. There seems to be no easy way to programmatically get the data from the InfoPath forms posted to the SharePoint server. I was fishing around in the database and found the Content column. I guessed this was the content of the InfoPath forms but your post confirmed.

    Now, you mention, “SharePoint and WSS APIs are the way to go” instead of SQL against the SharePoint db. Do you know off hand something I could look at to see how to locate and use the API to get the form data that way?
    Best regards,
    David

     
  2. Muzjee

    October 10, 2012 at 12:52 PM

    I am trying this script and getting an error on the following line
    Set rs = cn.Execute(selectStr)
    Error: Incorrect Syntax near “”
    code: 80040E14
    source: Microsoft OLE DB Provider for SQL Server

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: