Recovering Report Manager items with T-SQL

Are you stuck not having access to Report Manager when you need access to a report item on the server? Well thanks to this detail blog post on BretStrateham.com, we can figure out what we need to get back anything that is in the Reporting Services database. This happened one time I needed access to a report I had developed with Report Builder and needed it back in a hurry. The Report Manager website was showing security key errors, and I just wanted to be sure I had backup copies of my reports just in case we couldn't get Reporting Services back online. This post outlines the steps I took since I had read-access to the ReportServer database on the SSRS box.

Run this Query

BretStrateham.com has a lot of detail queries you can use, but here's my 'streamlined' version which you can run in SQL Server Management Studio (SSMS) against the ReportServer database to start the process of retrieving report server objects.

SELECT 
 ItemID, 
 Path, 
 Name, 
 CreationDate, 
 ModifiedDate, 
 CONVERT(xml, convert(varchar(max), convert(varbinary(max), content))) AS ContentXML
FROM Catalog 
WHERE Content IS NOT NULL

Save the ConvertXML results

Looking at the results above, you'll notice that the data in the ContentXML column appears as a hyperlink. Clicking on this highlighted text will open a new query window with the XML inside. But before you do that, remember that report items in SSRS are all just XML files with different extensions. So copy the filename from the result set, click on the XML link, and do a Save As with the name + extension in double quotes to retrieve your file. Below are some of the extensions you'll likely use if you ever have to go through this. Feel free to comment if I'm missing any.

Report Item	Extension
Report	.rdl
Data Source	.ds
Report model	.smdl

Hope this helps any of you in a pinch. Thanks for reading.