Cfrgtkky

I have a SSRS reporting data backup which I have restored on my local machine.

I used a query which is returning the RDL raw XML from catalog table, but I found that server has more than 10 reports which even I am unable to view in production database.

My question is that where do I find RDL file, whereas Reporting Server is showing 72 RDL while catalog table in reporting database is showing only 10.

You can run a backup of all your deployed reports using the following script.

• Remote to the report server using the following command line

%systemroot%/system32/mstsc.exe

• Then save the following procedure as a .rss file and run it with the parameter parentFolder="" as a zero string to save the entire folder structure with all the reports.

Command Line:

rs -s http://localhost/reportserver -i D:ScriptsBackup_Reports.rss -e Mgmt2010 -v backupFolder="D:ScriptsBackupReports" -v parentFolder=""

Report Backup Procedure:

Public Sub Main()

    '--------------------------------------------------------------------------------------------------------------------

    ' Purpose:   Script to backup reports from a folder on ReportServer

    '            Save file as .rss extension and run using rs.exe from command line.

    ' Reference: http://bhushan.extreme-advice.com/back-up-of-ssrs-reports-using-rs-utility/

    '            https://docs.microsoft.com/en-us/sql/reporting-services/tools/rs-exe-utility-ssrs?view=sql-server-2017

    ' Example:   rs -s http://localhost/reportserver -i D:ScriptsBackup_Reports.rss -e Mgmt2010 -v backupFolder="D:ScriptsBackupReports" -v parentFolder="/IndividualReportFolderNameHere"

    '            rs -s http://localhost/reportserver -i D:ScriptsBackup_Reports.rss -e Mgmt2010 -v backupFolder="D:ScriptsBackupReports" -v parentFolder=""

    '--------------------------------------------------------------------------------------------------------------------

    Try

        rs.Credentials = System.Net.CredentialCache.DefaultCredentials

        Dim items As CatalogItem() = Nothing



        If String.IsNullOrEmpty(parentFolder) Then

            items = rs.ListChildren("/", True)

        Else

            items = rs.ListChildren(parentFolder, False)

        End If



        Console.WriteLine()

        Console.WriteLine("...Backup Started...")



        For Each item As CatalogItem In items

            If item.TypeName = "Report" Then

                Console.WriteLine(item.Path)

                Dim reportPath As String = item.Path

                parentFolder = Path.GetDirectoryName(item.Path) ' comment out this line to save the reports in one folder

                Dim reportDefinition As Byte() = rs.GetItemDefinition(item.Path)

                Dim rdlReport As New System.Xml.XmlDocument

                Dim Stream As New MemoryStream(reportDefinition)

                Dim backupPath As String = Path.Combine(backupFolder, Date.Now().ToString("yyyy.MM.dd") + "" + parentFolder)



                If (Not System.IO.Directory.Exists(backupPath)) Then

                    System.IO.Directory.CreateDirectory(backupPath)

                End If



                rdlReport.Load(Stream)

                rdlReport.Save(Path.Combine(backupPath, item.Name + ".rdl"))



                Console.WriteLine(item.Name + ".rdl")

            End If

        Next



        Console.WriteLine("...Backup Completed...")

        Console.WriteLine()



    Catch e As Exception

        Console.WriteLine(e.Message)



    End Try



End Sub