Recovering Report Manager items with T-SQL

http://js/shCore.jshttp://js/shBrushCSharp.jshttp://js/shBrushXml.jsdp.SyntaxHighlighter.ClipboardSwf = ‘/flash/clipboard.swf’; dp.SyntaxHighlighter.HighlightAll(‘code’); 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

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.