Recovering documents from a deleted Team Project portal site

By /

I’ll preface this by saying that up until the point that I needed to do this, i knew very little about the database structure that supports SharePoint. 

I intentionally deleted the SharePoint portal used by one of my Team Projects.  The reason for it was that I wanted to implement a brand new structure, along with a new Dashboard to support TFS 2010.  I backed up all the documents that I thought needed to be saved (so I thought) and went about deleting and deploying the new SharePoint project with the new structure (future blog post on that). 

Early the next day, I realized there were some documents that I never backed up…so this is how I recovered from that.  Note: there may be better ways to do this using SharePoint tools out there, but I don’t know any of them, this felt intuitively right…

I restored a copy of the wss_Content database in the TFS SQL Server from the previous night.  Looking through the table structure, I found 3 tables that seemed interesting: AllDocs, AllDocVersions, And AllDocStreams.

After poking around the tables, i came up with this:

select * from AllDocs
where DirName like 'Sites/MyTeamProject/Requirements'

That gave me a list of all subfolders within the document library that I wanted to restore. I grabbed the each ID from that query, and used it to find all the child documents this way:

select a.LeafName, Content from AllDocStreams ds inner join 
AllDocs a on ds.Id = a.Id
where ds.ParentId = '{my guid}'
and ds.DeleteTransactionId = 0x

This gives me a lits of all the document names, and their byte array for all documents within each directory.  It also gives me back only the ones that were not deleted previously (I don’t need to restore those).

Then I wrote a very quick utility to extract them:

            using (SqlConnection c = new SqlConnection("Data Source=.;Initial Catalog=wss_restore;Integrated Security=SSPI;"))
            {
                c.Open();
                using (SqlCommand co = new SqlCommand("select a.LeafName, Content from AllDocStreams ds inner join AllDocs a on ds.Id = a.Id where ds.ParentId = 'C075EE6D-C04E-4444-8765-F6CC54768AAC' and ds.DeleteTransactionId = 0x", c))
                using (SqlDataReader r = co.ExecuteReader())
                {
                    while (r.Read())
                    {
                        byte[] arrAttachmentData = (byte[])r["Content"];
                        MemoryStream ms = new MemoryStream(arrAttachmentData, false);
                        //StreamWriter s = new StreamWriter(mem);


                        FileStream fs = File.OpenWrite(string.Format(@"c:\temp\{0}", r["LeafName"].ToString()));
                        ms.WriteTo(fs);
                        //fs.Write(ms.GetBuffer(), 0, ms.Position);
                        fs.Close();


                    }
                }
            }

And that’s it…once I had the docs locally, i was able to easily upload them to my new SharePoint portal.

Scroll to Top