Download all SSRS reports from server at one steps

Here we will learn how to download all SSRS reports of a server with a single step.

Open your SQL server management studio and follow steps below

Step 1:

EXEC sp_configure 'show advanced options', 1 
GO
-- Update the currently configured value for advanced options. 
RECONFIGURE 
GO   
-- Enable xp_cmdshell 
EXEC sp_configure 'xp_cmdshell', 1 
GO   
-- Update the currently configured value for xp_cmdshell 
RECONFIGURE 
GO  
-- Disallow further advanced options to be changed. 
EXEC sp_configure 'show advanced options', 0 
GO  
-- Update the currently configured value for advanced options. 
RECONFIGURE 
GO

Step 2:

--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 
   --Prepare the query for download.   
   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
  EXEC SP_EXECUTESQL @TSQL 
END
Written by Mitesh Gadhiya