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
- xp_cmdshell should be enabled. (Part 1).
- SQL Engine Account should have proper write permission on Output Folder, where you will get all your reports.
- 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\'
-
- 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
- LIKE COALESCE('%/%' + @ReportFolderPath + '%/%', '/' + CL.[Path] + '/') AND CL.Name LIKE COALESCE('%' + @ReportName + '%', CL.Name) FOR XML PATH('')), 1, 1, '') SELECT @SqlQuery
-