I lost a survey answers! #SharePoint stuff

Disclaimer

Microsoft does not recommend to work directly with SharePoint databases.

The Context

Often, in the current activity of a company, we need surveys to study the opinion of colleagues, employees or customers. Any such process requires a stage of questionnaire design, approval, testing and publishing. But, unfortunately, it may cause errors in the workflow approval. As we all know, the most common errors are typo mistakes.

The Mistake

The questionnaire is published, users began to fill in the data, possibly anonymous (no login), and someone noted that there was a mistake in the text. The temptation of the author or owner of the questionnaire is to go into the settings and change incorrect response options.

A classic mistake, because after the change, if you access the Graphical Summary of survey, we will see a situation like the following.

As you see, even though the questionnaire is in Romanian language, there are several answers (134) and the percentage shown in the graphical representation is different from 100%.

Quick solution

When you change the options in a survey question, the old values are no longer displayed. The good news is that the data is in the database and can be extracted from there with a simple select statement.

1. Get the survey ID

Go into Survey Settings and copy from your browser’s address bar the last part of the URL. Sample: List=%7B8D291BD5-1C23-4C58-8CED-67E7D0914C2C%7D

You need only the string with bold into my sample. %7B = { and %7D = }

2. Identify the content database for your current site.

On a SharePoint server run in PowerShell: Get-SPContentDatabase -site <URL>

Sample result:

Id               : 7c750508-9196-4821-9670-59ea2cc03efd
Name             : P_PortalFEAA_Content
WebApplication   : SPWebApplication Name=P_Portal
Server           : bdsrv-02
CurrentSiteCount : 2

3. Get data back!

On database server open SQL Server Management Studio and open a new query window and run the next SQL statement:

SELECT *
  FROM [P_PortalFEAA_Content].[dbo].[UserData]
  where tp_ListId = ‘8D291BD5-1C23-4C58-8CED-67E7D0914C2C’
 

You will obtain a list of records with all your answers. Select all records, Copy and Paste into an Excel worksheet and manipulate data as you want.

Now all the answers are “back”.

Learnings

Never modify variants of questions from a survey after it has been published!