Download all reports from SSRS report services

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  

--Replace NULL with keywords of the ReportManager's Report Path,

--if reports from any specific path are to be downloaded

DECLARE @FilterReportPath AS VARCHAR(500) = NULL

 

--Replace NULL with the keyword matching the Report File Name,

--if any specific reports are to be downloaded

DECLARE @FilterReportName AS VARCHAR(500) = NULL

 

--Replace this path with the Server Location where you want the

--reports to be downloaded..

DECLARE @OutputPath AS VARCHAR(500) = 'D:\Reports\Download\'

 

--Used to prepare the dynamic query

DECLARE @TSQL AS NVARCHAR(MAX)

 

--Reset the OutputPath separator.

SET @OutputPath = REPLACE(@OutputPath,'\','/')

 

--Simple validation of OutputPath; this can be changed as per ones need.

IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = ''

BEGIN

  SELECT 'Invalid Output Path'

END

ELSE

BEGIN

 

   SET @TSQL = 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].[dbo].[Catalog] CL ' +

                      ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' +

                      ' WHERE ' +

                      ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x'''

                    FROM

                      [ReportServer].[dbo].[Catalog] CL

                    WHERE

                      CL.[Type] = 2 --Report

                      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')

                      AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name)

                    FOR XML PATH('')), 1,1,'')

 

  --SELECT @TSQL

 

  --Execute the Dynamic Query

  EXEC SP_EXECUTESQL @TSQL

END