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
