17

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?

Joe
  • 531
  • 2
  • 7
  • 16

7 Answers7

45

Try this tool: http://code.google.com/p/reportsync/

It can sync all reports(or selected folders) from one server to another. In addition you can also download to and upload from local folders.

Data sources with the same name will automatically get attached. Saves a lot of time as you don't have to reattach datasources after uploading.

Note: I wrote this tool myself to suit my needs, but its free and open source.

nunespascal
  • 17,584
  • 2
  • 43
  • 46
  • 1
    Your tool is incredible. – Johnny_D Nov 28 '13 at 12:07
  • 1
    I know this is an old post but thanks for this tool it's great! – Richard Nixon Mar 24 '15 at 10:34
  • 2
    I'll add a thank you here, simple yet fantastic tool! – Rocket04 Feb 29 '16 at 19:32
  • 1
    Hi nunespascal, thank you very much for sharing a great tool! Is there more information about ReportSync. I mean something like what "**Download**", "**Upload**", "**Sync**" buttons. What is behind those buttons? Like what they do? If "**Download**" and "**Upload**" are more or less understandable, then "**Sync**" is not very clear. Any help and references would be much appreciated. Thanks. – Oleksii Dniprovskyi May 30 '18 at 19:11
  • 1
    Sync will sync selected reports(or folders) from the source to the destination server. After transferring the reports to destination, reportsync will attempt to find data source with the same name as what was used in source server and attach them to the report at destination. Doing this using the SSRS UI was a tedious process. – nunespascal Jun 06 '18 at 08:14
  • 2
    This tool worked great for me to! If there is something else like it, I haven't come across it. I downloaded the source code files from Github (https://github.com/phires/reportsync), ran VS, opened the solution file (.SLN), compiled the program, then got the executable file (.EXE) from the 'C:\Temp\reportsync-master\bin\Release' folder. SO reference: https://stackoverflow.com/questions/2962816/compile-to-stand-alone-exe-for-c-sharp-app-in-visual-studio-2010. – SherlockSpreadsheets Sep 11 '18 at 13:41
7

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.

Aaron Mason
  • 310
  • 3
  • 11
adolf garlic
  • 3,034
  • 7
  • 39
  • 54
  • That is a very useful tool but now I have to get someone to give me permissions to login to the server so that I can run it locally on the server in order for it to work. – Joe Mar 23 '11 at 12:46
  • You don't need to log on locally in order to run the tool to generate the scripts - if you can connect to Report Manager and have sufficient permissions it will run fine. I'm not even 100% sure that you need to be local to run the scripts that do get generated. – adolf garlic Mar 23 '11 at 13:23
  • What's the name of this tool? – snapplex Apr 08 '13 at 16:54
  • 1
    That site has gone but 30 seconds of searching on google gave me this: http://sqlserverfinebuild.codeplex.com/wikipage?title=Install%20Reporting%20Services%20Scripter – adolf garlic Apr 15 '13 at 14:09
  • 1
    Name is RSScripter. – Sam Feb 27 '17 at 16:28
  • 1
    @aaronmason Thank you for the link to the archive. – Kent Anderson Feb 13 '18 at 17:18
4

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.

flash
  • 388
  • 4
  • 15
2

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

Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • I am moving reports from Reporting Services 2005 to 2008 R2. Unfortunately copying the ReportServer database is not an option because new reports have been created and put on the new server and these will be lost if I replace the database with the one from the old server. – Joe Mar 18 '11 at 04:55
1

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.

ThiefMaster
  • 310,957
  • 84
  • 592
  • 636
Chris Almond
  • 11
  • 1
  • 1
    Be careful of this process. IF anything goes wrong, I bet Microsoft won't provide support. I'm not saying this won't work; I'm just saying this isn't anywhere close to a vendor approved process. Test heavily. – Jamie F Oct 11 '12 at 16:01
1

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.

Joe
  • 531
  • 2
  • 7
  • 16
0

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

Syed Fahad Ali
  • 59
  • 1
  • 4