Friday, March 27, 2009

Exporting Files from SharePoint Content Database

Murphy's Law can apply anywhere anytime and my recent re-installation of SharePoint is certainly no exception to the rules.

Soon after I finished recreating our SharePoint intranet, I stared sharing it with my immediate colleagues. While I was not really expecting some "Oh!" and "Aaah!" cheerful comments, I was still hoping for some kind of "Cool, it's back, thanks for the tool.".

But no, even that was expecting too much. The very first comment I received was an assassin comment like: "And I suppose you managed to retrieve all files stored in the previous SharePoint database ?". That comment triggered a machine-gun like stare from me to my colleague. No, I did not manage so far to retrieve these files and yes I had tried already. SQL Management Studio did not really help me by the way.

I had been cornered and really had to find a solution this time. So I returned to the web, googling frantically, trying various unsuccessful solutions until finally my patience and stubbornness got rewarded. I found found a great article from Keith Richie describing how to export the site content from a SharePoint Database for recovery purpose.

His article include a small source code and instruction to compile and run the program. It worked almost like a charm from the first run. Yet, as some of the recovered files were quite large, I had to boost the buffer size (as I worked locally this was not a problem) and even tweak the SQL query to work on a per directory basis to limit the execution run time. Happily this time SQL Server Management Studio was of great help to test and fine-tune the queries. Within 30 minutes from the moment I read Keith's post I had recovered all files I needed.

Today all files are imported in the new SharePoint and I preciously kept the program source and instruction ... just in case one day I need to recover files from another SharePoint database.


Recover Deleted Files said...

Restoring data from failed SharePoint server is not an easy task. A third party SharePoint database recovery software may help the users to get upto 100 % data in such type situations. One of the best and globally used application can be downloaded from here:

Anonymous said...

Here is a little script to export File-Blobs as Files from a sharepoint database directly (without Sharpoint Web running)

' VBS to read a BLOB (actually Image) column in SQL Server and save each row's image to a file.
' Example here reads PDFs stored in DocImage.Image column and saves each to a PDF file.
set oFS=CreateObject("Scripting.fileSystemObject")

sub MakePath(p)
if oFS.FolderExists(p) then exit sub
if i=0 then return
WScript.Echo("Create '"+p+"'")
end sub

Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2

if WScript.Arguments.Count < 3 then
WScript.Echo "Sorry, you have to enter some details for export:"
WScript.Echo "BlobExport.vbs "
WScript.Echo " is the SQL-Server name and instance e.g. 'SPSRV\SQLEXPRESS'"
WScript.Echo " is the name ob the sharpoint database"
WScript.Echo " is the source path - without starting URL"
WScript.Echo " is the destination path for the export (for example 'D:\Willi')"
WScript.Quit 0
end if

sqlserver = WScript.Arguments.Item(0)
database = WScript.Arguments.Item(1)
sourcepath = WScript.Arguments.Item(2)
outPath = WScript.Arguments.Item(3)
WScript.Echo "SQL-Server: "+sqlserver
WScript.Echo "Database : "+database
WScript.Echo "Source : '"+source+"'"
WScript.Echo "Dest : "+outPath

Set rs = CreateObject("ADODB.Recordset")

cn = "Provider=SQLOLEDB;Data Source="+sqlserver+";Initial Catalog="+database+";Integrated Security=SSPI;"
sql = "SELECT [DirName],[LeafName],DS.Content FROM AllDocStreams DS INNER JOIN AllDocs D ON (D.Id=DS.Id AND D.InternalVersion=DS.InternalVersion) WHERE D.DirName LIKE '"+sourcePath+"%'"

rs.Open sql, cn

do while Not rs.EOF
Set stream = CreateObject("ADODB.Stream")
dest=outPath + replace(cstr(rs.Fields("Dirname").Value),"/","\")
filename=dest+"\"+ cstr(rs.Fields("LeafName").Value)
WScript.Echo ("Download: "+filename)
With stream
.Type = adTypeBinary
.Write rs.Fields("Content").Value
.SaveToFile filename, adSaveCreateOverWrite
End With

Kaye R. Jenkins said...

SharePoint Server Data Recovery software which is associated with intranet, document management and with content management. Due to several reasons, it gets corrupted like virus attack, system shutdown automatically etc.

Learn More:-