SSRS - Download All Report Files Or Single (RDL) From ReportServer

Hi friends! Today, my manager asked me to download all the reports from ReportServer and check in to TFS.

There were plenty of reports -- almost 1,000 -- so it was difficult to download one by one. I thought this kind of problem comes to most DBAs, so I decided to write this blog. I found this script on Google and modified it-

Prerequisites & Notes

xp_cmdshell should be enabled. (Part 1).
SQL Engine Account should have proper write permission on Output Folder, where you will get all your reports.
Please read comments before execution and follow (Part 2).

Change the ReportServer database on the query.

Part 1

Enable xp_cmdshell

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

Part 2

Script Implementation

/*
If you want all reports in output folder whether they were published in different folders
in SSRS Report Server then put Null else define folder name
eg: DECLARE @ReportFolderPath AS VARCHAR(500) ='/Folder1/Folder2/'
*/

DECLARE @ReportFolderPath AS VARCHAR(500) = null
/*
If you want some specific report then put its name else put Null
eg: DECLARE @ReportName AS VARCHAR(500) = 'Bill'
*/
DECLARE @ReportName AS VARCHAR(500) = null
/*
This is the output folder where reports will be generated. Make sure Sql Server Engine Account
has proper permission on this
*/
DECLARE @OutputFolderLocation AS VARCHAR(500) = 'C:\ReportFolder\'
--To check Folder path
SET @OutputFolderLocation = REPLACE(@OutputFolderLocation, '\',' / ')
/*
This for bcp access to sql server. If you want to access through sql login use
eg : DECLARE @BCPComponents VARCHAR(1000)='-S"MachineName\SQL2012" -U"SA" -P"Pa$$word"'
or If you want from windows account having permission on sql then
eg : DECLARE @BCPComponents VARCHAR(1000)='-S"1MachineName\SQL2012"'
else null
*/
DECLARE @BCPComponents VARCHAR(1000) = null DECLARE @SqlQuery AS NVARCHAR(MAX) IF LTRIM(RTRIM(ISNULL(@OutputFolderLocation, ''))) = ''
BEGIN SELECT 'Access denied!!!'
END ELSE BEGIN SET @SqlQuery = STUFF((SELECT ';EXEC master..xp_cmdshell '
'bcp " ' + ' SELECT ' + ' CONVERT(VARCHAR(MAX), ' + ' CASE ' + ' WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'
''
''
''
') ' + ' ELSE C.Content ' + ' END) ' + ' FROM ' + ' [ReportServer$SQL2012].[dbo].[Catalog] CL ' + ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' + ' WHERE ' + ' CL.ItemID = '
''
'' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''
''
' " queryout "' + @OutputFolderLocation + '' + CL.Name + '.rdl" ' + '-T -c -x ' + COALESCE(@BCPComponents, '') + ''
''
FROM[ReportServer$SQL2012].[dbo].[Catalog] CL WHERE CL.[Type] = 2--Report AND '/' + CL.[Path] + '/'
LIKE COALESCE('%/%' + @ReportFolderPath + '%/%', '/' + CL.[Path] + '/') AND CL.Name LIKE COALESCE('%' + @ReportName + '%', CL.Name) FOR XML PATH('')), 1, 1, '') SELECT @SqlQuery
--Execute the Dynamic Query EXEC SP_EXECUTESQL @SqlQuery END

4 Comments

Join the conversation! Your thoughts help the community grow.

Juan Ramirez4y ago
But on your line = 36
Juan Ramirez4y ago
Msg 102, Level 15, State 1, Line 78Incorrect syntax near '+'.
Juan Ramirez4y ago
Vimal, came across your scripts, but having issues with the second one:
Rahul Neekhra8y ago
Error is coming Msg 102, LeMsg 102, Level 15, State 1, Line 36Incorrect syntax near '+'. vel 15, State 1, Line 36Incorrect syntax near '+'.