How to recover SharePoint document once deleted from recycle bin


In the scenario whereby a document has been deleted from both levels of the SharePoint recycle bin, you may have a request to recover it. Assuming you have a SQL database backup of the content database which hosted the document, you can get it back with minimal effort by following the steps below.

Important: Do not run these steps on the production SQL Server deployment. The idea is that this is done on a development or testing SQL Server environment where the content database which holds the document you wish to extract is restored to. This is because these steps are not officially supported by Microsoft.

Step 1: Get TextCopy utility and make sure it works

The textcopy utility comes with the SQL Server 2000 resource kit and is designed to run on SQL Server 2000. However it can be used on SQL Server 2005. Get textcopy.exe from the resource kit and place it in a folder on the SQL Server 2005 machine e.g. C:\Temp. Also add into this folder a file from a SQL Server 2000 installation called ntwdblib.dll. Both of these files need to be in the same folder to work.

Run textcopy.exe to ensure it works, you should see a list of textcopy commands.

Step 2: Determine the content database to use for extracting the document

In order to extract the document you need to know where the document was located in the site structure. For example, below we see a document titled “CoreIOModels”, which is hosted in the sub site “Docs” in the root site collection of http://moss.litwareinc.com web application. The document is in the documents document library.

Document in document library

You now need to determine which content database holds the document. This is done easily through Site Collection List option in the central administration application. Here you can see that the root site collection for http://moss.litwareinc.com is found in the content database WSS_Content_MOSS. You need the name of the content database because SQL scripts (and textcopy) will run against this database. Also, you now know which database to restore to a test / development environment instead of restoring all you SQL databases.

Site Collection List

Step 3: Determine the record which holds the binary image of the document to extract

Run the following script on the database, using SQL Query Analyser in SQL Server 2005. This script will return all the records which hold document that you are looking. You may get more than one record returned, as their might be several previous versions of the document.

USE [@database]

SELECT AllDocStreams.Id, AllDocStreams.[Content], AllDocStreams.Size, AllDocs.Version, AllDocs.TimeLastModified, AllDocs.CheckoutUserId,

AllDocs.CheckoutDate, AllDocs.IsCurrentVersion, AllDocs.DirName, AllDocs.LeafName, AllDocs.[Level]

FROM AllDocs INNER JOIN

AllDocStreams ON AllDocStreams.Id = AllDocs.Id AND AllDocs.[Level] = AllDocStreams.[Level]

WHERE (AllDocs.DirName = @dirname) AND (AllDocs.LeafName = @leafname)

Variables

@leafname = filename

@dirname = directory name of file

For example in my scenario above this would be

USE WSS_Content_MOSS

SELECT AllDocStreams.Id, AllDocStreams.[Content], AllDocStreams.Size, AllDocs.Version, AllDocs.TimeLastModified, AllDocs.CheckoutUserId,

AllDocs.CheckoutDate, AllDocs.IsCurrentVersion, AllDocs.DirName, AllDocs.LeafName, AllDocs.[Level]

FROM AllDocs INNER JOIN

AllDocStreams ON AllDocStreams.Id = AllDocs.Id AND AllDocs.[Level] = AllDocStreams.[Level]

WHERE (AllDocs.DirName = ‘docs/documents’) AND (AllDocs.LeafName = ‘coreiomodels.doc’)

This returns the following records in my case:

SQL Query Results

If you get multiple records returned, you will want to use the Version field and the isCurrentVersion and perhaps the TimeLastModified fields to determine which record in the one you want to extract.

Take note of the ID of the record any other unique field data from the AllDocStreams table so that you can uniquely identify the record in the AllDocStreams table.

Step 4: Extract the document using Textcopy

From the command prompt run the textcopy.exe command to extract the document (from the content field) in the AllDocStreams table.

Example TextCopy cmd to extract file:

textcopy /s @server /u @user /P @password /d “@database" /t docs /c content /F c:\temp\filename /O /Z /W "where ID= ‘@IdofRecord’ and Level=’@levelofrecord’”

Variables

@database = content database

@server = name of SQL Server machine to use

@leafname = filename

@dirname = directory name of file

@IdofRecord = Id of the content record to extract

@levelofrecord = level of content record to extract

For example, in my scenario this would be:

textcopy.exe /S MOSS /D wss_content_moss /T alldocstreams /C content /U sa /P pass@word1 /F c:\temp\coreidmodels.doc /O /Z /W "where ID=’2381F6A5-0AED-4F28-A031-BAC6E08B151F’ and Level=’255’"

This will dump the file into the C:\Temp folder and allow me to email or place the file onto the site for the user who needs it.

Comments (9)

  1. Introduction A couple years ago I wrote a prior post on how a simple VBS script can be used to extract

  2. F says:

    You’re article saved my ass. Big thx man for working this out 🙂

  3. Rakesh Kumar says:

    I have one requirement where I am stuck-

    In our application

    1. There is one Document Library(MyDL) in the Sharepoint site where several documents was stored(with relevant metadata like Created date, title etc).

    2.As business required, Documents should be moved to Archive folder from MyDL on the semester basis.

    3.Now all documents from MyDL has been moved to Archive folder on semester schedule (15 Oct 2010).

    4.Here we have all new metadata(e.g Created date, title etc) of the documents in the Archive folder(with on or after 15 Oct 2010).

    5.Now our requirement is , we need to get original Created date, modified date etc of the documents that was available in previous doc lib that is MyDL(before 15 Oct 2010 information needed)

    Please suggest if any approach is available to get metadata details for documents that were stored in MyDL (prior to Archiving)

    I will really apreciate your Help

  4. Praphulla Chaudhari says:

    Somebody deleted a file from WSS 3.0 server. its not available in the recycle bin of website. how to find who has deleted the file from server? and how to recover it?

  5. restore a list item which is stored as an attachment says:

    I have a list item to which i have attached a file. is there a way to recover the attached content from the database.

  6. What would cause the error – ERROR: Problem with file C:[my file name.doc]?

  7. Keith Wise says:

    I found this website on the Google offering a free trial version of software, i would suggest you to try this software.  http://www.undeletepcfiles.com/sharepoint-database-recovery.html

  8. loydking says:

    If you have facing any problem of corrupt SharePoint database, then you can effectively repair and recover your all files from SharePoint database successfully. Go to :-   www. recovery deletedfiles.com/ sharepoint-server -data- recovery.html

  9. Yuv says:

    Hi,

    we have sharepoint 2010 farm. I am trying to recover deleted or missing version history from a library/list. Here is the situation. we have a library, first the versioning was set up to unlimited adn then some goes and change it to limited to 10. When i observerd this, i changed it back to unlimited. Now some of the versions are missing, how to recover them. I really aprreciate.