Extract SSRS RDL files from Reporting database












1














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.










share|improve this question




















  • 2




    Can you not just grab the RDLs straight from the SSRS Server?
    – Alan Schofield
    Nov 17 '18 at 0:03










  • Are you sure you not selecting using TOP 10? Folders are also stored in the Catalog table, so there will be more entries than reports. However if the numbers still don't match I think you must have a different database than the report server. As you appear to have access to the report server, why not just download the definitions directly as @AlanSchofield has already mentioned?
    – Dale Burrell
    Nov 18 '18 at 20:47










  • @AlanSchofield I wish I could have but due to security reasons I am having limited access.
    – Rahul Neekhra
    Nov 19 '18 at 5:24
















1














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.










share|improve this question




















  • 2




    Can you not just grab the RDLs straight from the SSRS Server?
    – Alan Schofield
    Nov 17 '18 at 0:03










  • Are you sure you not selecting using TOP 10? Folders are also stored in the Catalog table, so there will be more entries than reports. However if the numbers still don't match I think you must have a different database than the report server. As you appear to have access to the report server, why not just download the definitions directly as @AlanSchofield has already mentioned?
    – Dale Burrell
    Nov 18 '18 at 20:47










  • @AlanSchofield I wish I could have but due to security reasons I am having limited access.
    – Rahul Neekhra
    Nov 19 '18 at 5:24














1












1








1


1





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.










share|improve this question















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.







sql-server reporting-services ssrs-2008






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 19 '18 at 10:01









aduguid

2,13661031




2,13661031










asked Nov 16 '18 at 19:01









Rahul Neekhra

6021627




6021627








  • 2




    Can you not just grab the RDLs straight from the SSRS Server?
    – Alan Schofield
    Nov 17 '18 at 0:03










  • Are you sure you not selecting using TOP 10? Folders are also stored in the Catalog table, so there will be more entries than reports. However if the numbers still don't match I think you must have a different database than the report server. As you appear to have access to the report server, why not just download the definitions directly as @AlanSchofield has already mentioned?
    – Dale Burrell
    Nov 18 '18 at 20:47










  • @AlanSchofield I wish I could have but due to security reasons I am having limited access.
    – Rahul Neekhra
    Nov 19 '18 at 5:24














  • 2




    Can you not just grab the RDLs straight from the SSRS Server?
    – Alan Schofield
    Nov 17 '18 at 0:03










  • Are you sure you not selecting using TOP 10? Folders are also stored in the Catalog table, so there will be more entries than reports. However if the numbers still don't match I think you must have a different database than the report server. As you appear to have access to the report server, why not just download the definitions directly as @AlanSchofield has already mentioned?
    – Dale Burrell
    Nov 18 '18 at 20:47










  • @AlanSchofield I wish I could have but due to security reasons I am having limited access.
    – Rahul Neekhra
    Nov 19 '18 at 5:24








2




2




Can you not just grab the RDLs straight from the SSRS Server?
– Alan Schofield
Nov 17 '18 at 0:03




Can you not just grab the RDLs straight from the SSRS Server?
– Alan Schofield
Nov 17 '18 at 0:03












Are you sure you not selecting using TOP 10? Folders are also stored in the Catalog table, so there will be more entries than reports. However if the numbers still don't match I think you must have a different database than the report server. As you appear to have access to the report server, why not just download the definitions directly as @AlanSchofield has already mentioned?
– Dale Burrell
Nov 18 '18 at 20:47




Are you sure you not selecting using TOP 10? Folders are also stored in the Catalog table, so there will be more entries than reports. However if the numbers still don't match I think you must have a different database than the report server. As you appear to have access to the report server, why not just download the definitions directly as @AlanSchofield has already mentioned?
– Dale Burrell
Nov 18 '18 at 20:47












@AlanSchofield I wish I could have but due to security reasons I am having limited access.
– Rahul Neekhra
Nov 19 '18 at 5:24




@AlanSchofield I wish I could have but due to security reasons I am having limited access.
– Rahul Neekhra
Nov 19 '18 at 5:24












1 Answer
1






active

oldest

votes


















2














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





share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53343897%2fextract-ssrs-rdl-files-from-reporting-database%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2














    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





    share|improve this answer




























      2














      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





      share|improve this answer


























        2












        2








        2






        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





        share|improve this answer














        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






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 18 '18 at 22:20

























        answered Nov 18 '18 at 22:08









        aduguid

        2,13661031




        2,13661031






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53343897%2fextract-ssrs-rdl-files-from-reporting-database%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            How to change which sound is reproduced for terminal bell?

            Can I use Tabulator js library in my java Spring + Thymeleaf project?

            Title Spacing in Bjornstrup Chapter, Removing Chapter Number From Contents