HELP! Recover an SSRS rdl "file" that was overwritten

Epicor ERP 10
1

I created a new version of a BAQ Report, and accidentally overwrote an existing SSRS RDL “file”.

I say “file” as I believe that the report definition doesn’t really exist in a file, but rather as data in the SSRS DB. That correct?

Any way to get that previous RDL definition back?

[ fingers crossed ]

1 Like
2

I don’t suppose you guys have scheduled DB backup do you?

3

We do backups of the DB’s that hold the main ERP data (our production and test DBs), and of the whole server.

Which DB would the RDL defs be in? “System Databases”, “RDB”, “ReportServer” ??

4

I’ve found the rdl’s in localhost/ReportServer.

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

That sound right?

5

Okay… I got the ReportServer DB restored to a new DB “ReportServer_0608”, on the same server.

I was going to use the tool ReportSynch, but it cannot find the new DB. I get:

Apparently I need to enable some service so the new DB is visible via HTTP. Any hints?

6

As far as I know ReportSync goes through SSRS.

7

Is my only options to create another AppServer, but point the Reports to this newly created DB?

I just need to get one RDL out of the restored DB. Any quick way to do that?

8

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.

9

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.

10

I went ahead an used some tricks I found online. The following SQL code extracts the RDL contents in XML format

  1. Restored a backup of “ReportServer DB” to “ReportServer_0608”
  2. 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
  1. Saved the Query Result to a text file
  2. Stripped out all but the ‘ContentXML’ field
  3. Saved it as a text file ‘ScafGL6_recovered.rdl’
  4. Used SSRS Report Manager to upload the RDL file to the ReportServer
  5. Updated the ReportStyle to use “ScafGL6_recovered”

So far so good!

6 Likes