How to restore a deleted report on reporting services?

First get the copy of the ReportServer database backup and restore it. Use the following step to restore.

  1. In SSMS right click the database folder then select restore.
  2. Give a new name for the new restored version of the database. Example:ReportServer2.
  3. If you have to use check boxes to find the point in time to restore to but if you have a full backup then restore the full copy.
  4. Click files section and select the path where the mdf and ldf file should be stored.
  5. Click options and make sure to uncheck Take tail-log backup.
  6. Click OK to complete the restore.

Next you want to extract the XML for the deleted report in the restored copy of the database (ReportServer2).

Use the following query to get the XML content.

SELECT
Name as ReportName, CONVERT(Varchar(Max),CONVERT(VARBINARY(MAX),Content)) AS ReportContent
FROM ReportServer.dbo.Catalog Where Content is NOT NULL

Select the ReportContent XML data and copy it to notepad. Review the very first few character and make sure it starts with “<?xml version=”1.0″……” Remove any extra characters prior if they exists.

Save the XML content as ReportName.rdl

You can then go to the reporting server site and upload the report. If subscriptions existed then it will have to be recreated. You can also delete the restored database (Ex: ReportServer2).