Recovering a deleted Reporting Services Report

Yes, I deleted the wrong report while housekeeping 🙄. This raised the question as to how to recover it once it has been deleted? There is no recycle bin to or undelete option in reporting services on the version I work with and at the time of writing (Nov 2017).

It turns out to be very simple and quick to restore, if you have backups of your “ReportServer” database, which of course every one has diligently kept right?!

Option 1 – Restore to point in time

The simplest way is to restore the “ReportServer” database from backup, to a point in time just before the deletion, but this would loose any changes on the report server made since it was deleted. In my case I had spent a few hours after deleting the report doing some general housekeeping. I didn't want to lose all the good work that I had done and had changed all kinds of things on the report server, so this was not desirable - I would loose that work.

Option 2 – Restore the single report from backup

Slightly more involved as an option, but still quick and easy, follow the steps below, it only takes a few minutes to do.

1. We restore a backup of the reportserver database
2. Extract the XML that represents the report that got deleted
3. Import that XML report into the live reporting serivces user interface.
4. Set the report schedule and subscription up again, if it had one. You could pull what they are from the restored database too. In my case I was happy to just manually set them up again.

Restore the “ReportServer” database

• Right click the database in SSMS, select restore
• Rename the Destination Database to ReportServerRestored
• Use the check boxes to find the point in time to restore to, here I chose to not apply the logs to make for a speedy restore
• Click Files on page selector in left hand side. If the default path is not appropriate (under Restore As, in the grid, you may need to scroll left to see), then change the path that the database files will be restored to
• Click options on page selector in left hand side to select options. Unselect Take tail-log backup.
• Click ok to start the restore

Extract the XML that represents the report from the restored database

Reports are stored in a binary field in a table in the database as XML. The following article explains how to get at the XML that defines the reports,

Extracting SSRS Report RDL (XML) from the ReportServer database

Using the information in this article we can get to the report we need by running the script in SSMS.

--The first CTE gets the content as a varbinary(max)  
--as well as the other important columns for all reports,  
--data sources and shared datasets.  
WITH ItemContentBinaries AS  
(  
  SELECT  
     ItemID,Name,\[Type\]  
    ,CASE Type  
       WHEN 2 THEN 'Report'  
       WHEN 5 THEN 'Data Source'  
       WHEN 7 THEN 'Report Part'  
       WHEN 8 THEN 'Shared Dataset'  
       ELSE 'Other'  
     END AS TypeDescription  
    ,CONVERT(varbinary(max),Content) AS Content  
  FROM ReportServerRestored.dbo.Catalog  
  WHERE Type IN (2,5,7,8)  
),  
--The second CTE strips off the BOM if it exists...  
ItemContentNoBOM AS  
(  
  SELECT  
     ItemID,Name,\[Type\],TypeDescription  
    ,CASE  
       WHEN LEFT(Content,3) = 0xEFBBBF  
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))  
       ELSE  
         Content  
     END AS Content  
  FROM ItemContentBinaries  
)  
--The outer query gets the content in its varbinary, varchar and xml representations...  
SELECT  
   ItemID,Name,\[Type\],TypeDescription  
  ,Content --varbinary  
  ,CONVERT(varchar(max),Content) AS ContentVarchar --varchar  
  ,CONVERT(xml,Content) AS ContentXML --xml  
    
FROM ItemContentNoBOM  
where name like '%{enter part of report name here}%'

Note you need to edit the {enter par of report name here} to be what it says, you will then be presented with the row(s) of interest. Work out which is the report you need if multiple rows are returned (note the restored database name is embedded into the SQL, change if you restored to another name).

On the row, example shown above, click the hyperlink of the column “ContentXML” to open the XML in an XML editor within SSMS (cool feature). Then go to File>>Save As.. within SSMS to save to a drive for later import to the report server.

Rename the xml file

Rename the xml file as a .rdl file you can make the filename an appropriate descriptive name for the report as this will later show in the report server

Upload the report definition file to the server

Upload the .rdl file to the reports folder on the reporting services server. Navigate to the folder it should reside in and select “Upload File” as shown below

Recreate subscriptions and schedules

You will have to manually set up subscription schedules again for the report if they existed before.

Delete the restored database

From SSMS right click the database name and select delete to remove it. Check that the database files are also deleted on the server.

The report is now restored and the task is completed!

If you found this useful please comment, it helps motivate me to keep adding to the blog…