SSRS - Download all Report files Or Single (RDL) from Report Server

Hi friends! Today my manager asked me to download all reports from Report Server and check in to TFS.

There were plenty of reports almost 1000, so it is difficult to download one by one. I thought this kind of problem come to most of the DBAs so decided to write this blog, I found same script on google and modified it-

Prerequisites & Notes :

1. xp_cmdshell should be enabled. (Part 1)

2. SQL Engine Account should proper write permission on Output Folder where you will get all your reports  

3. Please read comments before execution and follow (Part 2)

4. Change the Report Server 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

Reference : VinayPugalia