Learn how to manage SSRS Reports

01-Content_Download All SSRS Reports From a Report Server Folder.ps1

# In this gist, we create a PowerShell script that, when run, will prompt the user for the following variables:
# * $SSRSServer [The server name or domain name of the report server]
# * $SSRSPath [The SSRS folder path for the folder containing the .rdl files you wish to download]
# * $Destinationpath [The local folder you wish to store the downloaded .rdl files in]
#   
# When run, the user will supply the variables and the script will go out to the report server and individually download every .rdl file from that folder on the report server to the user's local machine in the location specified.

# Set Variables from User Input
$SSRSServer = Read-Host("Enter the report server name or domain name (Without http:// or any subdirectories)") 

Write-Host("Enter the path to the report folder containing the .rdl files you want to download.")
Write-Host("Example: /FinancialForms/Invoice")
$SSRSPath = Read-Host("Path: ")

$DestinationPath = Read-Host("Enter the full path to the local folder you want to download the reports to")
$DestinationPath = $DestinationPath.Trim('"')
$DestinationPath = $DestinationPath.TrimEnd("\")

# Confirm your selections
Write-Host("")
Write-Host("")
Write-Host("CONFIRMATION:")
Write-Host("")
Write-Host("Downloading all reports from")
Write-Host("     http://$SSRSServer/reportserver") -ForegroundColor Red -BackgroundColor Yellow
Write-Host("that are stored in the")
Write-Host("     ""$SSRSPath""") -ForegroundColor Red -BackgroundColor Yellow
Write-Host("folder.")
Write-Host("")
Write-Host("Saving a copy of all .rdl files from this location to the ")
Write-Host("     ""$Destinationpath""") -ForegroundColor Red -BackgroundColor Yellow
Write-Host("folder.")
$confirm = Read-Host("Are you sure you want to continue? [Y]/[N]")

# Download the Reports
if ($confirm.ToLower() = "y")
{
    $reportServerUri = "http://$SSRSServer/reportserver/ReportService2010.asmx?wsdl"
    $rs = New-WebServiceProxy -Uri $reportServerUri -UseDefaultCredential -Namespace "SSRS"

 
    # Download all Reports from a specific folder to .rdl files in the current 
    # directory.
    $items = $rs.ListChildren($SSRSPath, $false)
    $items | Where-Object { $_.TypeName -eq "Report" } | Foreach-Object {
        $filename = ("{0}.rdl" -f $_.Name)
 
        Write-Output ("Downloading ""{0}""..." -f $_.Path)
        $bytes = $rs.GetItemDefinition($_.Path)
        [System.IO.File]::WriteAllBytes("$DestinationPath\$filename", $bytes)
    }
} 

02-Restore SSRS RDL that was overwritten.sql

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


--3. Saved the Query Result to a text file
--4. Stripped out all but the 'ContentXML' field
--5. Saved it as a text file 'ScafGL6_recovered.rdl'
--6. Used SSRS Report Manager to upload the RDL file to the ReportServer
--7. Updated the ReportStyle to use "ScafGL6_recovered"

_Manage SSRS Reports.md

Manage SSRS Reports

In this gist, we

• Download an entire report folder of RDL definitions using PowerShell
• Restore an RDL file that was accidentally overwritten

Resources.md

• Use PowerShell to manage SSRS Reports
• Restore Overwritten SSRS Report