Here’s a short PowerShell script that :
1. Connects to your report server
2. Creates the same folder structure you have in your Report Server
3. Download all the SSRS Report Definition (RDL) files into their respective folders
In addition to backing up your Source Project, your ReportServer database, or good old RSScripter (see http://sqlserver-indo.org/blogs/mca/archive/2009/03/08/extract-and-transfer-rdl-files-from-ssrs.aspx) this is just another way you can “backup” or archive your reports.
#note this is tested on PowerShell v2 and SSRS 2008 R2 [void][System.Reflection.Assembly]::LoadWithPartialName("System.Xml.XmlDocument"); [void][System.Reflection.Assembly]::LoadWithPartialName("System.IO"); $ReportServerUri = "http://yourreportserver/ReportServer/ReportService2005.asmx"; $Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential ; #check out all members of $Proxy #$Proxy | Get-Member #http://msdn.microsoft.com/en-us/library/aa225878(v=SQL.80).aspx #second parameter means recursive $items = $Proxy.ListChildren("/", $true) | ` select Type, Path, ID, Name | ` Where-Object {$_.type -eq "Report"}; #create a new folder where we will save the files #PowerShell datetime format codes http://technet.microsoft.com/en-us/library/ee692801.aspx #create a timestamped folder, format similar to 2011-Mar-28-0850PM $folderName = Get-Date -format "yyyy-MMM-dd-hhmmtt"; $fullFolderName = "C:Temp" + $folderName; [System.IO.Directory]::CreateDirectory($fullFolderName) | out-null foreach($item in $items) { #need to figure out if it has a folder name $subfolderName = split-path $item.Path; $reportName = split-path $item.Path -Leaf; $fullSubfolderName = $fullFolderName + $subfolderName; if(-not(Test-Path $fullSubfolderName)) { #note this will create the full folder hierarchy [System.IO.Directory]::CreateDirectory($fullSubfolderName) | out-null } $rdlFile = New-Object System.Xml.XmlDocument; [byte[]] $reportDefinition = $null; $reportDefinition = $Proxy.GetReportDefinition($item.Path); #note here we're forcing the actual definition to be #stored as a byte array #if you take out the @() from the MemoryStream constructor, you'll #get an error [System.IO.MemoryStream] $memStream = New-Object System.IO.MemoryStream(@(,$reportDefinition)); $rdlFile.Load($memStream); $fullReportFileName = $fullSubfolderName + "" + $item.Name + ".rdl"; #Write-Host $fullReportFileName; $rdlFile.Save( $fullReportFileName); } |
Enjoy!
How to Download All Your SSRS Report Definitions (RDL files) Using PowerShell,
Thanks, that’s really useful : )
I’m getting this error when running this script against both a SQL-2005 and a SQL-2008 SSRS instance:
The term ‘New-WebServiceProxy’ is not recognized as the name of a cmdlet, funct
ion, script file, or operable program. Check the spelling of the name, or if a
path was included, verify that the path is correct and try again.
My $ReportServerUri is defined correctly.
What could I be missing?
Thank you,
Marios Philippopoulos
This worked perfectly. You saved me at least a day of file->save-as’ing – thanks a million!!!
Worked great! Thanks
how could i pass the authentication(username, pass)?
if i’m going to download from remote computer.
Thanks B4.
I am also getting the error below.
The term ‘New-WebServiceProxy’ is not recognized as the name of a cmdlet, funct
ion, script file, or operable program. Check the spelling of the name, or if a
path was included, verify that the path is correct and try again.
Any thoughts as to what might be wrong?
What version of PowerShell are you using?
[…] http://www.sqlmusings.com/2011/03/28/how-to-download-all-your-ssrs-report-definitions-rdl-files-usin… […]
[…] for me, I stumbled upon an article by Donabel Santos (link here) which showed how to achieve half of what I was trying to do. I figured if I could script the […]
you are a life saver
Worked perfectly.. Thanks so much.
[…] As the custom Web Reports now available in Reporting Services we can download the custom reports as SSRS Report Definitions (RDL) files. I’ve used a PowerShell script which downloads all RDL files to a file location (http://www.sqlmusings.com/2011/03/28/how-to-download-all-your-ssrs-report-definitions-rdl-files-usin…). […]
Hello,
$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential ;
May I know what is the instance name use on this line? I’m referring with SSRS.ReportingService2005.
Excellent….really useful
Can we do the for upload instead of download.
Excellent….really useful
Can we do the same for upload instead of download.
nice Work :D…it worked perfectly…
Amazing! Works like Knife on Butter..
TY
Worked for me on SSRS 2012. Is it possible to download a single folder, because this script downloads everything on the report server.
Couple things if you are using SSRS2008 R2; It really uses 2010 so ReportService2005.asmx should be ReportService2010.asmx. Also, you want to use -Namespace SSRS.ReportingService2010 instead.
RS2010 also uses $Proxy.GetItemDefinition($item.Path); instead.
Can you please provide a script to upload all the folders back to report server.
Thanks so much! Fantastic script.
Sorry, I’m a newbie when it comes to PowerShell… how to I run this script?
Can you provide detail instructions for those of us who are not familiar with PowerShell?
For those who run into a webserviceproxy error, I had the problem as well initially. I hadn’t realized that the “/ReportService2005.asmx” path on the end of my report server URL was important and had not included it at first; particularly because I am SSRS 2008 R2.
In any case, add that /ReportService2005.asmx back on if you took it off and that might help.
thanks a lot, it was so helpfull this article and the solution too!
The wired thing, was that i migrated time ago from 2005 to 2008, and I changed the code for the $ReportServerUri = “http://my server/ReportServer/ReportService2005.asmx”;
ReportService2008.asmx and didn’t work but i left has you have it with 2005 and works.
Excellent script. I used it against my SQL 2012 machine.
The only problem I had with it was that my currently logged in user didn’t have rights to the reports. By changing the $proxy” line as follows, I was able to specify the user, and input the password when prompted. Just replace the “YOURUSERNAMEHERE” text with the account you want to login with.
$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -Credential YOURUSERNAMEHERE ;
Thanks again for the great script, it was incredibly handy!
Is there a way to save not all rdl files but only files under specified subfolder?
Great script. Worked like charm! Thanks a lot!
Hi,
I find it very interesting but one thing I get errors when I used sharepoint as the ReportServerUrl.
Could you make a powershell to backup RDL files in SharePoint?
That’s great. Just downloaded 1300-odd definintions. Thankyou do much!
That’s great. Just downloaded 1300-odd definintions. Thankyou so much!
Thanks for the script! This saved me hours of work. For those that require authentication, replace -UseDefaultCredential with -Credential “Domainuserid”
Does not work if SharePoint is in Integrated mode!