Since I don’t have a backup of just that DB, I need to ask the IT Gods if they can pull just that DB out of a backup of the whole server, and restore it (just the ReportServer DB) with a new name (say “ReportServer_Temp”).
Then I can use ReportSync to download the RDL from localhost/ReportServer_Temp
AppServer wouldn’t do anything here. You’d need to have SSRS pointing to this new database, which if it’s a production server I’m guessing it’s not possible.
There are a couple tools that allow you to extract RDLs from a Report Server DB. Depending on your level of comfort with SSIS this may take longer than restoring the SSRS site to a test server and extracting the RDL using report manager.
I went ahead an used some tricks I found online. The following SQL code extracts the RDL contents in XML format
Restored a backup of “ReportServer DB” to “ReportServer_0608”
Ran the following SQL code (note the first FROM refers to ReportServer_0608)
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 reportserver_0608.dbo.catalog
WHERE Name='SacfGL6' AND type IN ( 2, 5, 7, 8 )),
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)
SELECT itemid,
name,
[type],
typedescription,
content,
CONVERT(VARCHAR(max), content) AS ContentVarchar,
CONVERT(XML, content) AS ContentXML
FROM itemcontentnobom
Saved the Query Result to a text file
Stripped out all but the ‘ContentXML’ field
Saved it as a text file ‘ScafGL6_recovered.rdl’
Used SSRS Report Manager to upload the RDL file to the ReportServer
Updated the ReportStyle to use “ScafGL6_recovered”