SQL Server Monitoring and Debugging Tip

Just a small tip. In case you need to see what is happening in you SQL Server such as I/O, Expensive Queries, resource waits etc you can use the Activity Monitor in SQL Server Management Studio:

https://msdn.microsoft.com/en-us/library/ms175518(v=sql.110).aspx

This has helped me finding if there are problem within my database and possible areas, functionalities which may slow down the database.

Advertisements

Lost files in Sharepoint Recycle Bin that can not be restored

This time I am writing a post about a problem that a client of mine had.

The problem is rather simple BUT required allot of time and energy to fix the problem or rather in my case to recover the items:

The client at some point by someone deleted files which ended up in the recycle bin. Later the client wanted to restore these files BUT was unable to. Sharepoint was telling that the restore operation failed even though you where able to clearly see that the files where available to restore in the Recycle bin of the site.

NOTICE: Before you start to try any of the things written in this post take notice TO NOT DO these things in you client product/live environment. If you chose to do so you will cause your client to lose their MS support. MS does not support manipulation of the content databases directly to SQL Server. So take a database backup, restore it in a test or development environment and fix you problem there.

NOTICE: Use this code at your own risk. The code does some crazy stuff directly to SharePoint content database. I will not take any responsibility on anything that you do with the application and/or any part of it and it’s code.

Here are the steps which I ended up taking and a more detailed explanation on them:

  1. Trying to restore the items through the object model
  2. Investigating the Sharepoint content database
  3. Trying to restore the files by fixing content database Recycle Bin table missing information in the files
  4. Trying to recover the files directly from the AllDocsStream table

Lets start from the first item and advance from there:

1. Trying to restore the items through the object model

This is basically the same as the UI BUT I decided to give it a quick try since nothing from the UI worked and sometimes you can do things through code that you can not do through the UI.

Long story short the object model did not offer much help and any restore operation failed again or nothing happened. For this a created a forms application(code is included at the end of this post).

Here is how the final version of the application looks like: appppp

In this first step I create the functionality in the “Blue” area. These where the object model fixes which did not help.

There basic functionality are provided which are in the “Blue” area:

  1. Restore the selected items from recycle bin
  2. Delete selected items
  3. And move them to the Site Collection Recycle Bin

2. Investigating the Sharepoint content database

At this point because the object model and the UI did not do what they are supposed to I simply gave up on using the “preferred” way of Microsoft and went directly into the content database. My first destination was the “Recycle Bin” table.

After a few moments of careful examination of the clients content database table I noticed that the documents that can not be restored have 4 table fields which are null while other data row in the table all have data in them.

The fields which where null where:

  1. ListId
  2. ListItemId
  3. AuthorId
  4. ListDirName

So I decided to create a code that would restore the missing values. I decided that the AuthorId and ListItemId would be defined by me during the code(since they could be anything any existing user and any integer number that is large enough not to conflict with existing ID enumeration in the target list) and the ListId and ListDirName are to be retrieved through the object model. This had to be done by retrieving the values of the following fields WebId, SiteId and DirName. In the code used these values to get the site, web and the list. After I got the list it was easy to just update the table with the list ID and the rootfolder ServerRelative URL into the ListDirName field.

You can find the code for this in the button named: “Repair Recycle Bin Items(DB)”

3. Trying to restore the files by fixing content database Recycle Bin table missing information in the files

Unfortunately what I did in step two was not enough because the sites and lists did not exist anymore for these deleted files that needed to be restored from the bin.

The next step was to try to update the following tables so that the document/file would be restored to a different web and list that actually exists.

I had to make modifications to the following tables:

  1. AllDocs = Data regarding the file/document itself, where it is, it’s parent, state etc
  2. AllDocStreams = The actual binary data of the file
  3. RecycleBin = The data of a deleted file that has ended up in the recycle bin

By retrieving the DocId value from RecycleBin table from a file that can not be restored you can correlate to the documents information in the AllDocs and AllDocStream.

This step is very complex and I due to time constraints I did not get it to work properly. Some piece of data was missing to get the file restored to another location. I managed to get the file restored BUT while the list showed that it had files in it you could not access the file. So something was missing from somewhere.

You can check the code for this in the button: “Repair Recycle Bin items(DB) to inserted URL”

4. Trying to recover the files directly from the AllDocsStream table

Finally the last step was to go directly into the AllDocsStream with ADO .NET, read the binary data, retrieve the documents real name from the RecycleBin table and write that data into the hard drive.

This was SUCCESSFUL!!! It worked like a charm and the client was happy to get their files restored.

In the AllDocsStrems table the most important field is the “Content” field. This is the actual binary data that you need to retrieve and use it to write it into your hard drive.

From the RecycleBin table you need the value from the LeafName field.

Using these two piece of information you can reconstruct the file.

Here is the code that solved my problem, you can find it behind the button: “Recover files from DB”

            

String connectionString = ConfigurationSettings.AppSettings[“DBConnectionString”];

            SqlDataReader rdr = null;

            this.textBox2.Text += “Opening connection to DB”;

            using (SqlConnection connection = new SqlConnection(connectionString))

            {

                connection.Open();

                this.textBox2.Text += “Connection opened”;

                List<RecycleBinDBItem> items = new List<RecycleBinDBItem>();

                try

                {

                    // Get all such items which can not be restored properly from the UI or by object model

                    SqlCommand cmd = new SqlCommand(“SELECT * FROM RecycleBin WHERE ListDirName IS NULL”, connection);

 

                    rdr = cmd.ExecuteReader();

                    this.textBox2.Text += “Select clause executed”;

 

                    while (rdr.Read())

                    {

                        this.textBox2.Text += “Reading line: \n”;

                        this.textBox2.Text += rdr[“DirName”].ToString();

                        items.Add(new RecycleBinDBItem(rdr[“DocId”].ToString(), rdr[“LeafName”].ToString()));

                    }

                }

                finally

                {

                    // close the reader

                    if (rdr != null)

                    {

                        rdr.Close();

                    }

                    int itemIDCounter = 5000;

                    int authorID = 1;

                    // Loop through every recycle bin items that is missing needed field data from the RecycleBin table to be restored properly

                    foreach (RecycleBinDBItem item in items)

                    {

                        try

                        {

                            SqlCommand cmd = new SqlCommand(String.Format(“SELECT Content FROM AllDocStreams WHERE Id = ‘{0}'”, item.docID ), connection);

 

                            // 2. Call Execute reader to get query results

                            byte [] bArray = (byte[])cmd.ExecuteScalar();

                            // Craete the file and write the binary data from the Database

                            FileStream fs = File.Create(item.fileName, (int)bArray.Length);

                            fs.Write(bArray, 0, bArray.Length);

                            fs.Flush();

                            fs.Close();

                        }

                        catch (SqlException sqlex)

                        {

                            this.textBox2.Text += sqlex.Message + ” #######\n” + sqlex.StackTrace;

                        }

                        catch (Exception ex)

                        {

                            this.textBox2.Text += ex.Message + ” #######\n” + ex.StackTrace;

                        }

                        finally

                        {

                            if (rdr != null)

                            {

                                rdr.Close();

                            }

                        }

                    }

                }

            }

            this.textBox2.Text += “DB operations done!”;

Final words;

Hope this might help someone else who is suffering from this problem. The application and it’s code is not the prettiest BUT I did what I could in the time that was given to me and also what the client is ready to spend to solve this problem. It’s only a tool and should be treated as such.

FINAL NOTICE: Use this code at your own risk. The code does some crazy stuff directly to SharePoint content database. I will not take any responsibility on anything that you do with the application and/or any part of it and it’s code.

Check out the CodePlex Project for the code:

https://recyclebinrestoreapp.codeplex.com/