Download all SSRS reports

Question

I want to get a copy of all .rdl files in one server. I can do the download manually one report at the time, but this is time consuming especially that this server has around 1500 reports.

Is there any way or any tool that allows me to download all the .rdl files and take a copy of them?

Novosibirsk

Answer 1

There is a complete & simpler way to do this using PowerShell.

This code will export ALL report content in the exact same structure as the Report server. Take a look at the Github wiki for other options & commands

#------------------------------------------------------
#Prerequisites
#Install-Module -Name ReportingServicesTools
#------------------------------------------------------

#Lets get security on all folders in a single instance
#------------------------------------------------------
#Declare SSRS URI
$sourceRsUri = 'http://ReportServerURL/ReportServer/ReportService2010.asmx?wsdl'

#Declare Proxy so we dont need to connect with every command
$proxy = New-RsWebServiceProxy -ReportServerUri $sourceRsUri

#Output ALL Catalog items to file system
Out-RsFolderContent -Proxy $proxy -RsFolder / -Destination 'C:\SSRS_Out' -Recurse

Voracious

putting the $sourceRsUri in single quotes didn't work for me, I had to use double quotes – Consumer

I was getting Unable to find type [Microsoft.ReportingServicesTools.ConnectionHost] - this is defined in the ReportingServicesTools package. Turned out the root cause was running the script from a network path. Once I ran it from my C drive, it worked fine. – Ogden

Works for both SSRS 2017 and 2019 too! For SSRS 2017 - $sourceRsUri = 'ReportServerURL/ReportServer/ReportService2017.asmx?wsdl' For SSRS 2019 - $sourceRsUri = 'ReportServerURL/ReportServer/ReportService2019.asmx?wsdl' – Exarchate

i'm having a weird one. i specified $sourceRsUri to look at /ReportService2012.asmx?wsdl because i'm running SSRS 2012 but when running the script, it keeps looking at /ReportService2010.asmx?wsdl and therefore throwing an error. – Lapstrake

Hey Nii, ssrs 2012 s ill uses the 2010 asmx so you need to keep it set to that. Do you see another error when using that? – Voracious

FYI: This PowerShell module does not work with PowerShell Core: github.com/microsoft/ReportingServicesTools/issues/… – Tug

Another FYI: if anyone else is still crazy enough to have SSRS 2005 running - this won't work out-of-the-box as the SOAP API was quite different back then. I was able to hack the behaviour of Out-RsFolderContent and Out-RsCatalogItem to just download reports successfully (not Data Sources etc). – Tug

This is working for SSRS 2019, however I am hit by "The permissions granted to user ''domain_name\USER" are insufficient for performing this operation. I am not the administrator and not able to change these rights. If there is any workaround with this code, would be happy to retrieve it. – Clicker

Please add the Flag -Credential with a username that his admin rights to the server if working in a remote machine as outlined in this answer: #51249945 – Danielledaniels

Answer 2

I've created this powershell script to copy them into a ZIP. You have to provide the SQL server database details.

Add-Type -AssemblyName "System.IO.Compression.Filesystem"

$dataSource = "SQLSERVER"
$user = "sa"
$pass = "sqlpassword"
$database = "ReportServer"
$connectionString = "Server=$dataSource;uid=$user; pwd=$pass;Database=$database;Integrated Security=False;"

$tempfolder = "$env:TEMP\Reports"
$zipfile = $PSScriptRoot + '\reports.zip'

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()

$allreports = $connection.CreateCommand()
$allreports.CommandText = "SELECT ItemID, Path, CASE WHEN Type = 2 THEN '.rdl' ELSE '.rds' END AS Ext FROM Catalog WHERE Type IN(2,5)"

$result = $allreports.ExecuteReader()

$reportable = new-object "System.Data.DataTable"
$reportable.Load($result)
[int]$objects = $reportable.Rows.Count
foreach ($report in $reportable) {
    $cmd = $connection.CreateCommand()
    $cmd.CommandText = "SELECT CAST(CAST(Content AS VARBINARY(MAX)) AS XML) FROM Catalog WHERE ItemID = '" + $report[0] + "'"
    $xmldata = [string]$cmd.ExecuteScalar()
    $filename = $tempfolder + $report["Path"].Replace('/', '\') + $report["Ext"]
    New-Item $filename -Force | Out-Null
    Set-Content -Path ($filename) -Value $xmldata -Force
    Write-Host "$($objects.ToString()).$($report["Path"])"
    $objects -= 1
}

Write-Host "Compressing to zip file..."
if (Test-Path $zipfile) {
    Remove-Item $zipfile
}
[IO.Compression.Zipfile]::CreateFromDirectory($tempfolder, $zipfile) 

Write-Host "Removing temporarly data"
Remove-Item -LiteralPath $tempfolder -Force -Recurse

Invoke-Item $zipfile

Bakunin

this ended up helping me instead of the accepted answer. so thanks! – Lapstrake

Answer 3

Found and used this without any issues. Nothing to install, just added my url, and pasted into Powershell.

https://microsoft-bitools.blogspot.com/2018/09/ssrs-snack-download-all-ssrs-reports.html

In case the link breaks, here's the code from the link:

###################################################################################
# Download Reports and DataSources from a SSRS server and create the same folder
# structure in the local download folder.
###################################################################################
# Parameters
###################################################################################
$downloadFolder = "c:\temp\ssrs\"
$ssrsServer = "http://myssrs.westeurope.cloudapp.azure.com"
###################################################################################
# If you can't use integrated security
#$secpasswd = ConvertTo-SecureString "MyPassword!" -AsPlainText -Force
#$mycreds = New-Object System.Management.Automation.PSCredential ("MyUser", $secpasswd)
#$ssrsProxy = New-WebServiceProxy -Uri "$($ssrsServer)/ReportServer/ReportService2010.asmx?WSDL" -Credential $mycreds
 
# SSRS Webserver call
$ssrsProxy = New-WebServiceProxy -Uri "$($ssrsServer)/ReportServer/ReportService2010.asmx?WSDL" -UseDefaultCredential
 
# List everything on the Report Server, recursively, but filter to keep Reports and DataSources
$ssrsItems = $ssrsProxy.ListChildren("/", $true) | Where-Object {$_.TypeName -eq "DataSource" -or $_.TypeName -eq "Report"}
 
# Loop through reports and data sources
Foreach($ssrsItem in $ssrsItems)
{
    # Determine extension for Reports and DataSources
    if ($ssrsItem.TypeName -eq "Report")
    {
        $extension = ".rdl"
    }
    else
    {
        $extension = ".rds"
    }
     
    # Write path to screen for debug purposes
    Write-Host "Downloading $($ssrsItem.Path)$($extension)";
 
    # Create download folder if it doesn't exist (concatenate: "c:\temp\ssrs\" and "/SSRSFolder/")
    $downloadFolderSub = $downloadFolder.Trim('\') + $ssrsItem.Path.Replace($ssrsItem.Name,"").Replace("/","\").Trim() 
    New-Item -ItemType Directory -Path $downloadFolderSub -Force > $null
 
    # Get SSRS file bytes in a variable
    $ssrsFile = New-Object System.Xml.XmlDocument
    [byte[]] $ssrsDefinition = $null
    $ssrsDefinition = $ssrsProxy.GetItemDefinition($ssrsItem.Path)
 
    # Download the actual bytes
    [System.IO.MemoryStream] $memoryStream = New-Object System.IO.MemoryStream(@(,$ssrsDefinition))
    $ssrsFile.Load($memoryStream)
    $fullDataSourceFileName = $downloadFolderSub + "\" + $ssrsItem.Name +  $extension;
    $ssrsFile.Save($fullDataSourceFileName);
}

Masseter

Answer 4

This is based on SQL2016/SSRS2016 but I think it should work for 2012.

SELECT 'http://mySQLServerName/reports/api/v1.0/catalogitems(' + cast(itemid as varchar(256))+ ')/Content/$value' AS url
    FROM ReportServer.dbo.Catalog

This will give you a list of URL's, one for each report.

If the above did not work in SSRS 2012 then go to the report manager and do as if you were going to download the file from there. Check the URL on the download button and you'll probably see a URL with and item id embedded int it. Just adjust the above code to match that url structure.

What you do with then after this is up to you. Personally I would use the Chrome extension called 'Tab Save' available in the Chrome store here. You can simply copy and paste all the URL's created above into it and hit the download button...

Hundred

Answer 5

If you just need this for backup purposes or something similar, this might be useful: Where does a published RDL file sit?

The relevant query from that thread is:

select convert(varchar(max), convert(varbinary(max), content))
from catalog
where content is not null

The original answer was using 2005, and I've used it on 2016, so I imagine it should work for 2008 and 2012.

When I had to use this, I added in the Path to the query as well, so that I knew which report was which.

CAVEAT: prior to SSMS v18, Results to Grid is limited to 64KB per tuple and Results to Text are limited to 8,192 characters per tuple. If your report definition is larger than these limits you will not be able to get the entire definition.

In SSMS v18, those limits have been increased to 2MB per tuple for both Reports to Grid as well as Results to Text.

Arsenic

Answer 6

I'vr tried several permutations of this script and keep getting the "can't create proxy connection" error. Here's the one that "should" work:

#------------------------------------------------------
#Prerequisites
#Install-Module -Name ReportingServicesTools
#------------------------------------------------------

#Lets get security on all folders in a single instance
#------------------------------------------------------
#Declare SSRS URI
$sourceRsUri = "http://hqmnbi:80/ReportServer_SQL08/ReportService2010.asmx?wsdl"

#Declare Proxy so we dont need to connect with every command
$proxy = New-RsWebServiceProxy -ReportServerUri $sourceRsUri

#Output ALL Catalog items to file system
Out-RsFolderContent -Proxy $proxy -RsFolder / -Destination 'C:\Users\arobinson\source\Workspaces\EDW\MAIN\SSRS\HQMNBI' -Recurse

This is the error I'm getting:

Failed to establish proxy connection to http://hqmnbi/ReportServer_SQL08/ReportService2010.asmx : The HTML document does not contain Web service discovery information. At C:\Program Files\WindowsPowerShell\Modules\ReportingServicesTools\0.0.6.6\Functions\Utilities\New-RsWebServiceProxy.ps1:136 char:9

•     throw (New-Object System.Exception("Failed to establish proxy ...
  

•     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : OperationStopped: (:) [], Exception
  • FullyQualifiedErrorId : Failed to establish proxy connection to http://hqmnbi/ReportServer_SQL08/ReportService2010.asmx : The
    HTML document does not contain Web service discovery information.

I've tried the URI with htttp:// and without, I've tried including the port number. etc. Still can't get this to actually work. We have two other SSRS instances that I was able to run this against no problem.

Ursala

Answer 7

From this question: SQL Reporting Services - COPY reports to another folder

I found this tool can both download and upload reports. Plus it lists out folders and subfolders.

http://code.google.com/p/reportsync/

Exarchate

Answer 8

regarding this issue: Failed to establish proxy connection to http://hqmnbi/ReportServer_SQL08/ReportService2010.asmx : The HTML document does not contain Web service discovery information. At C:\Program Files\WindowsPowerShell\Modules\ReportingServicesTools\0.0.6.6\Functions\Utilities\New-RsWebServiceProxy.ps1:136 char:9

Had the same error so i Used this instead: Instead of this line : $sourceRsUri = "http://hqmnbi:80/ReportServer_SQL08/ReportService2010.asmx?wsdl"

use this :

$sourceRsUri = "https://hqmnbi:80/ReportServer_SQL08/ReportService2010.asmx?wsdl"

then all worked no prob - proxy and all

Lighter

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Estimation

Answer 9

I tried using the accepted answer but had some problems. So I wrote a script to use the REST SSRS endpoints. This script will work with Powershell 7.x on Windows. I think it will work with Powershell on other platforms if path separators are changed appropriately.

#------------------------------------------------------
#Prerequisites
#Install-Module -Name ReportingServicesTools
#------------------------------------------------------

$sourceRsUri = 'https://ssrs.ourcompany.com/reports'
$localRoot = 'C:\Development\MySSRSProject\Server RDL Backup'

# create all the folders first.
$folders = Get-RsRestFolderContent / -Recurse -ReportPortalUri $sourceRsUri | Where-Object Type -eq 'Folder'
foreach($folder in $folders)
{
    # create each folder locally
    $localFolder = $localRoot + $folder.Path
    if (!(Test-Path $localFolder)) {
        New-Item -ItemType Directory -Path $localFolder
    }
}

# download the reports
$reports = Get-RsRestFolderContent / -Recurse -ReportPortalUri $sourceRsUri | Where-Object Type -eq 'Report'
# clear out modified date file:
Clear-Content ($localRoot + "\lastModified.txt")
foreach($report in $reports)
{
    # download each report to appropriate folder
    $localFolder = $localRoot + $report.Path

    # replace '/' with '\' in path
    $localFolder = $localFolder.Replace('/', '\')

    #remove report name from path
    $localFolder = $localFolder.Substring(0, $localFolder.LastIndexOf("\"))

    Out-RsRestCatalogItem $report.Path -ReportPortalUri $sourceRsUri -Destination $localFolder -Overwrite

    # write report last modified date and by to a file:
    $outString = $report.Path + " last modified by " + $report.ModifiedBy + " on " + $report.ModifiedDate.ToString("yyyy-MM-dd HH:mm:ss")
    $outString | Out-File -FilePath ($localRoot + "\lastModified.txt") -Append

}

Micron