locked
Subscription migration from SQL Server 2005 to SQL Server 2008R2 RRS feed

  • Question

  • Hi,

    I have to migrate all the SSRS Report subscriptions existing on SQL Server 2005 to SQL Server 2008 R2 on different box. does microsoft provide any tool to automate this process or what is the way to move in this direction?

    Thanks in Advance,

    M Chandra

    Tuesday, January 3, 2012 11:05 AM

Answers

  • Hi,

    Here is one free tool. Have a look:

    http://www.sqldbatips.com/showarticle.asp?ID=62


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Proposed as answer by swapnil Musale Tuesday, January 3, 2012 9:01 PM
    • Marked as answer by Zilong Lu Thursday, January 5, 2012 7:31 AM
    • Unmarked as answer by M Chandra Thursday, January 5, 2012 7:40 AM
    • Marked as answer by M Chandra Friday, January 6, 2012 4:54 AM
    Tuesday, January 3, 2012 11:28 AM
  • Chandra,

    Subscriptions are helded in the report server database. You can move the data which will move the subscriptions. Keep in mind the data in the report server database is not made for human consumption, so it is a little cryptic.

    When we migrated servers from 2005 to 2008 we backup reportserver database in 2005 and restored in 2008. The tool is called Reporting Services Scripter and can be downloaded at RSScripter.  I've used it to move RDL files along with the subscriptions.

    Let me know if you have troubles.


    Thanks,
    Sandip Shinde
    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Marked as answer by Zilong Lu Thursday, January 5, 2012 7:32 AM
    Tuesday, January 3, 2012 11:30 AM

All replies

  • Hi,

    Here is one free tool. Have a look:

    http://www.sqldbatips.com/showarticle.asp?ID=62


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Proposed as answer by swapnil Musale Tuesday, January 3, 2012 9:01 PM
    • Marked as answer by Zilong Lu Thursday, January 5, 2012 7:31 AM
    • Unmarked as answer by M Chandra Thursday, January 5, 2012 7:40 AM
    • Marked as answer by M Chandra Friday, January 6, 2012 4:54 AM
    Tuesday, January 3, 2012 11:28 AM
  • Chandra,

    Subscriptions are helded in the report server database. You can move the data which will move the subscriptions. Keep in mind the data in the report server database is not made for human consumption, so it is a little cryptic.

    When we migrated servers from 2005 to 2008 we backup reportserver database in 2005 and restored in 2008. The tool is called Reporting Services Scripter and can be downloaded at RSScripter.  I've used it to move RDL files along with the subscriptions.

    Let me know if you have troubles.


    Thanks,
    Sandip Shinde
    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Marked as answer by Zilong Lu Thursday, January 5, 2012 7:32 AM
    Tuesday, January 3, 2012 11:30 AM
  • Thanks a lot Asim and Sandip.

     

    I found this tool very useful. But I am having one problem in using this tool. I am unable to find out the .asmx file for SSRS 2008 and may be that's why it is pointing to same reoprt server where i am transferring it from.

     

    My 2008 report server instance name is MCHANDRA\MSSQLSERVER1

    Report manager: http://mchandra:8080/Reports_2008/Pages/Folder.aspx

    For 2005 server this tool automatically detected this file at http://localhost/ReportServer/ReportService2005.asmx

    Thanks,

    M Chandra

    Thursday, January 5, 2012 7:49 AM
  • Hi,

    Try with following Web Service for 2008

    http://localhost/ReportServer/ReportService2010.asmx

    Or

    http://mchandra:8080/ReportServer/ReportService2010.asmx


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Edited by Asim Bagwan Thursday, January 5, 2012 8:13 AM
    Thursday, January 5, 2012 8:12 AM
  • Thanks Asim,

     

    Below link worked for me.

    http://mchandra:8080/ReportServer_2008/ReportService2005.asmx

    Monday, January 9, 2012 5:32 AM
  • Hi Asim,

    i am facing some strange issues. I tested all this migration from 2005 to 2008 and worked fine. But when trying to do same for 2008R2 log file is getting populated with messages like below

    Reporting Services Scripter Load Log 2.0.0.17
     
    Starting Load at Mon 01/09/2012 21:18:47.05
    SCRIPTLOCATION = 
    REPORTSERVER   = http://dweb89ntv/Reportserver_RSAPD
    BACKUPLOCATION = 
    SCRIPTLEVEL    = SQL2005
    TIMEOUT        = 60
    RS             = "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\RS.EXE"
     
    Running script "TLVReporting\TLV_DataSource.rds.rss"
    The system cannot find the path specified.
     
    Running script "Mitchell Reports\WorkCenter Total Loss\TL Claim Summary.rdl.rss"
    The system cannot find the path specified.
     
     
    Finished Load at Mon 01/09/2012 21:18:47.07

     

    Please suggest.

     

    Thanks,

    M Chandra
     

    Tuesday, January 10, 2012 5:22 AM
  • M Chandra

    When I came across this error, the "path" in question was the path to the RS executable.

    Since I was on a 64-bit machine, I had to add " (x86)" to the RS line:

    SET RS="C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\RS.EXE"

    Thursday, April 12, 2012 12:38 AM
  • I do not have RS.EXE installed on my machine. My machine is  PRODUCTION SERVER and I will not get the permissions to install it. Please help me in getting through it. I get the scripts created, but I am not sure if they are right to restore them on to another machine. I see similar values like M Chandra, below:

    SET LOGFILE="RS Scripter Load Log.txt"
    SET SCRIPTLOCATION=
    SET BACKUPLOCATION=
    SET REPORTSERVER=http://localhost/ReportServer
    SET RS="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\RS.EXE"
    SET TIMEOUT=60

    Also, do I have to have the IP address for the server name in REPORTSERVER param?


    Thank You!


    • Edited by mkakashif Tuesday, September 25, 2012 8:30 PM
    Tuesday, September 25, 2012 8:30 PM