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?

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

Answer 2

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.

Answer 3

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

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...