Delen via

Recover Documents from SharePoint 2003 Database

  • Artikel

This will not work with MOSS 2007. If you are looking for a MOSS 2007 solution for recovering document go here: 

Recover Documents from MOSS 2007 Database

At some point a file will be accidentally deleted from a SharePoint library. There are tools to guard against unintentionally file deletions such as the Recycle Bin for SharePoint 2003, but it's not installed with the product out of the box. Incidentally, MOSS '07 comes with a recycle bin. However, with SPS 2003/WSS 2.0 if a user deletes a file most often the recovery path is to restore a backup copy of the appropriate content database and extract the file. Building the backup environment can be time consuming. Instead, the file can be extracted by going directly against a restored copy of the content database with a simple VB Script. This approach is rather old school. It could have been done with a .NET exe to do the job, but it takes so little code that it didn't seem worthwhile to put together a compiled assembly and include parameter handling logic.

A few disclaimers are necessary. This script goes directly against a SharePoint 2003/WSS 2.0 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. This will not work with MOSS 2007. The need for this kind of approach should be reduced with the introduction of the MOSS 2007 Recycling Bin.

With that out of the way, let's proceed. The script queries the Docs table which contains the application documents and retrieves the most current version based on the document name which is then streamed out as binary data to a file:

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=SQLOLEDB;data Source=[SERVERNAME];Initial Catalog=[DATABASE_NAME];Trusted_Connection=yes"
Set rs = cn.Execute("SELECT Content FROM Docs WHERE LeafName = 'FILENAME.EXT'")
Set mstream = CreateObject("ADODB.Stream")
mstream.Type = 1
mstream.Open
mstream.Write rs.Fields("Content").Value
mstream.SaveToFile "C:\FILENAME.EXT", 2
rs.Close
cn.Close

Copy this code into Notepad and ,naturally , replace your [SERVERNAME], [DATABASE_NAME], and FILENAME.EXT with appropriate values. Save this file as a VBS script and execute from the command line as:

C:\>CSCRIPT ExtractDoc.vbs

Note that the SQL Query is rather straight-forward. It simply looks for a file according to the LeafName which corresponds to the document name. The code assumes that there will be at least one row returned. It doesn't do any check should the query return zero rows. It's recommended that the query executed in the script is first tested in SQL Query Analyzer to ensure that you retrieve the expected single result. The same file name found in more than one document library or folder you'll have multiple results. There's no guarantee that the first file returned will be the file you want. Use other values in the Docs table to narrow the file down to one, then paste the desired query into the script.

This is certainly not something to use in a production environment where automated document retrieval must be a repeatable and reliable process. But it is a quick and dirty means of extracting a document from a restored database that spares you from the overhead of restoring a SharePoint/WSS environment.

Update: I recently came across a post for SPExplorer. It's a tool for walking a SharePoint 2003 database directly rather than through an API and related blog that mentions SP Explorer mentions that it has the ability to extract documents.

Comments

  • Anonymous
    June 23, 2008
    Introduction A couple years ago I wrote a prior post on how a simple VBS script can be used to extract

  • Anonymous
    July 24, 2008
    This script help me alot to recover some of the very important data that was accidentally got deleted. Thanks a lot!!!

  • Anonymous
    September 25, 2008
    This code also helped me.  Finding a single document in some utilites is difficult.  This code makes it easy.  Good job.

  • Anonymous
    October 12, 2009
    look out for Kernel for SharePoint Server software for sharepoint document recovery. Its more result-oriented for deleted documents.. http://www.nucleustechnologies.com/sharepoint-document-recovery.html

  • Anonymous
    December 23, 2009
    Great piece of code and instruction. Saved me a ton of time, could easily recover the document that was deleted.

  • Anonymous
    May 09, 2010
    Cheers for a very usefull and informative blog. Actually the script saved my weekend :-) Thanks!