How do I copy SSRS reports to a new server if I am

Question

I have to copy all reports from one SSRS server to another. I plan on doing this by going through each report and downloading the report definition file and then uploading the file to the new server.

I have been given System Administrator and System User site wide roles and I have Browser, Content Manager, My Reports, Publisher and Report Builder roles for the Home folder but I still can't see the Edit button that will allow me to save the report definition file. The reason is because the reports are owned by other users. I can't ask all the users to give me permissions to edit their reports because there are too many users and I think a lot of them will never get round to doing it.

What can I do to copy all the reports to the new server if I am not the owner of most of them?

Answer 1

If you can consider replacing all reports on the new server, you should look at moving the ReportServer database. This will also move subscriptions and cached data: http://technet.microsoft.com/en-us/library/ms156421.aspx

What version of SSRS are you using? The edit button was in SSRS 2005, but is no longer in 2008 or 2008 R2: it's replaced with the "Download" button. Could that be the problem?

As a Content Manager, you should be able to edit the definitions of any report.

Let me know what version you're looking at. Jamie F

Answer 2

What I ended up doing is running Internet Explorer as the server administrator user. You do this by holding shift and right-clicking on the Internet Explorer icon on your start menu and then choosing "Run as different user". You then enter the login details for the domain administrator user account and enter the address of the report server. Being the server adminstrator user for the domain allowed me to be the Content Manager user for all of the reports.

Answer 3

First: backup your your new Reports database before you do this. Copy the follwing tables from the original Reports database to the new Reports database: - Catalog - ChunkData - DataSource - Policy - PolicyuserRole - SecData - Users

Make sure you dont copy the Keys table!

One issue with this is you will need to re-create all your shared datasources and re-assign them to each report. But, this will copy over all your folders, reports, and user roles.

Answer 4

SSRS uses SQL Server to store it’s details as a backend and the Catalog table is used to store the report file in binary form. The below script simply pulls the report definition from the Catalog table & uses BCP utility to export the same at a pre-defined path as a .rdl file.

To use the BCP utility from TSQL, we need to execute “xp_cmdshell” command; it is disabled by default. So, first you need to execute the below script to enable it -

-- Allow advanced options to be changed. 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

Once successfully executed, the below script with the required changes could be executed to download the files -

DECLARE @FilterReportPath AS VARCHAR(500) = NULL DECLARE @FilterReportName AS VARCHAR(500) = NULL

DECLARE @OutputPath AS VARCHAR(500) = 'D:\Reports\Download\'

DECLARE @TSQL AS NVARCHAR(MAX) SET @OutputPath = REPLACE(@OutputPath,'\','/')

IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = '' BEGIN SELECT 'Invalid Output Path' END ELSE BEGIN 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,'')

EXEC SP_EXECUTESQL @TSQL END

Answer 5

For SQL Server Reporting Services 2008 R2 or later, Microsoft have a migration tool:

http://www.microsoft.com/en-us/download/details.aspx?id=29560

Reporting Services Migration Tool

A tool that migrates reports and other artifacts from one report server to another report server. It can also be used as a backup and restore tool for Reporting Services.

I haven't made personal use of this, and to be honest the description lists a few failings that the developers are 'working towards a solution' on, but it might help someone out.

Answer 6

Use this

Just point it at your RS server and let it run. It has many options as to what is and isn't scripted. One of which is download existing RDL file.

When complete just a find and replace tool to change the server name (within the generated scripts) and any other password/location information and let it run. It is essentially using RS.exe under the hood.

I seem to recall that you have have to run it locally on the SSRS box when deploying.