Back Up of SSRS Reports
By using the "SQL Server Reporting Services RS.EXE", we can take the Back Up of SSRS Report folder and move the reports from one server to another server. The SQL Server Reporting Services RS.EXE utility is a command line utility that can perform many scripted operation related to SQL Server Reporting Services (SSRS).
In this article we are going to see how to use the ""SQL Server Reporting Services RS.EXE"" to take Back Up of SSRS Report. The SQL Server Reporting Services RS.EXE utility is a command line utility that can perform many scripted operation related to SQL Server Reporting Services (SSRS). It can be used to complete various administrative tasks including publishing reports and moving reports from one server to another server. The RS utility is a script hosting utility that uses VB.NET script, so if you're familiar with VB.NET and scripting, it's relatively easy to create .rss files to perform just about any task that you might want to perform. The list of actual tasks that can be performed is quite endless and includes:
• Deploying / Publishing reports
• Moving reports
• Exporting reports to a file
• Adjust security
• Cancel a running job
• Configure SSRS system properties
1. File Location
RS.exe is located at \Program Files\Microsoft SQL Server\110\Tools\Binn. You can run the utility from any folder on your file system.
You can pass values into your rss scripts by using Global Variables. You don't declare these variables you simply refer to a non-declared "variable" in your rss script and RS is smart enough to know that you want to set them at the command line. The way to set them is by using the -v argument. See below for another example.
rs.exe
-i FileName.rss
-s http://localhost/reportserver
-v rootPath="/Root"
-v DWServerName="localhost"
-v DWDatabaseName="DB Name"
-v sourceFilePath="D:\SSRS_Reports"
-e Mgmt2010
2. Here is the script for taking the Backup of SSRS Report files
Public Sub Main()
Try
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim Items as CatalogItem()
Dim Item as CatalogItem
Dim ReportName As String
Items = rs.ListChildren(ItemPath, false)
Console.Writeline()
Console.Writeline("Reports Back Up Started.")
For Each Item in Items
ReportName = ItemPath + "/" + Item.Name
Dim reportDefinition As Byte() = Nothing
Dim rdlReport As New System.Xml.XmlDocument
reportDefinition = rs.GetReportDefinition(ReportName)
Dim Stream As New MemoryStream(reportDefinition)
Dim curDate as Date = Date.Now()
Dim strDate as String = curDate.ToString("dd-MM-yyyy")
Dim ReportBackupNew as String = ReportBackup+"\"+strDate+"\"+ItemPath
If(Not System.IO.Directory.Exists(ReportBackupNew )) Then
System.IO.Directory.CreateDirectory(ReportBackupNew)
End If
rdlReport.Load(Stream)
rdlReport.Save(ReportBackupNew + "\" + Item.Name +".rdl")
Console.Writeline("Report " + Item.Name +".rdl Backed up Successfully")
Next
Console.Writeline("Reports Back Up Completed.")
Console.Writeline()
catch e As Exception
Console.Writeline(e.Message)
End Try
End Sub
Now save the above file into D:\ReportBackup directory with name ReportBackup.rss. This script will take each reports under the path we provide, iterate through all the reports , gets report definitions from the server we provide, and save it into .rdl file under the location we gave through command line.
3. Now again open Notepad and copy-paste following code.
set ItemPath=/SSRS_Reports
set ReportServerURL=http://localhost/Reportserver
set ReportBackup=D:\ReportBackup
rs -i "D:\ReportBackup\ReportBackup.rss" -s %ReportServerURL% -v ItemPath="%ItemPath%" -v ReportBackup="%ReportBackup%"
4. Now save the file into D:\ReportBackup directory with name ReportBackup.bat. In this script, ItemPath is the location where reports are stored in the report server. ReportServerURL is the URL of ReportServer where reports are deployed. ReportBackup is the location where backup of report is stored.
5. Now double click on ReportBackup.bat file and you will see result looks like following screenshot.
6. Now Open ReportBackup location. There you can find the folder with the name of current date in dd-mm-yyyy format which contains back up of all the reports. Please check below screen shot.
7. You can also execute batch file from windows task scheduler. So you can create one schedule which execute daily on particular time. This schedule will generate day-to-day folder with backup of reports into your ReportBackup.