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

1. xp_cmdshell should be enabled. (Part 1).
2. SQL Engine Account should have proper write permission on Output Folder, where you will get all your reports.
3. 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