locked
Transfer Subscription SQL Agent Jobs RRS feed

  • Question

  • I need to transfer a fairly large number of SSRS reports and subscriptions from one server to another. I've managed to transfer the reports, data sources, etc. by way of a detach/attach of the original SSRS database from Server A to Server B. I'm able to verify this by going into Report Manager and viewing all reports and their data sources, subscriptions, etc.

    What I am having an issue with is getting the actual SQL Server Agent Subscription jobs over to the new server. I had thought that stopping and restarting Reporting Services would be enough for SQL Agent to "pick up" the subscriptions, but to no avail.

    Is there an "easy" way to get the jobs to also transfer to a new server?

    Thanks!


    A. M. Robinson

    Sunday, July 5, 2015 1:50 PM

Answers

  • I took the SSIS route and that did the trick...

    I had some misgvings, but everything seems to work just fine!

    Thanks...!


    A. M. Robinson

    Wednesday, July 22, 2015 8:18 PM
  • use SSIS

    Transfer Jobs Task is used to transfer SQL Server Agent jobs from one SQL Server instance to another. This task gives you an option to copy all the jobs or selected jobs from the source server to the destination server.


    Vote As Helpful if it helps to solve your issue

    • Marked as answer by ansonee Wednesday, July 22, 2015 8:16 PM
    Sunday, July 5, 2015 5:39 PM

All replies

  • use SSIS

    Transfer Jobs Task is used to transfer SQL Server Agent jobs from one SQL Server instance to another. This task gives you an option to copy all the jobs or selected jobs from the source server to the destination server.


    Vote As Helpful if it helps to solve your issue

    • Marked as answer by ansonee Wednesday, July 22, 2015 8:16 PM
    Sunday, July 5, 2015 5:39 PM
  • I'm aware of that SSIS task, but my concern is if that task will keep all SSRS subscription/job settings intact.

    Would like to know if anyone has actually used that task to move SSRS subscription jobs to another server and if they had any success with it.


    A. M. Robinson

    Sunday, July 5, 2015 9:32 PM
  • select Encryption Keys, then click Backup. Enter a Password in the Encryption Key Information dialog and click the button next to Key File to specify a location and name for the key backup file.


    After Report server restoration and account verification

    restore the Encryption Key from the database you restored. In the Navigation panel of the Reporting Services Configuration Manager select Encryption Keys and click the Restore Button. In the Restore Encryption Key dialog click the ... button to locate the file you created when backing up the Encryption Key from the server you are migrating, enter the password you used and click the OK button.

    Vote As Helpful if it helps to solve your issue


    • Edited by AV111 Monday, July 6, 2015 3:25 AM
    Monday, July 6, 2015 3:21 AM
  • I believe if you go into "Reporting Server Configuration Manager" and to the "Database" section and click "Change Database" and point it as your restored databases, it should scan for the subscriptions and recreate the jobs for you.
    Monday, July 6, 2015 1:47 PM
  • I've already made the key backup and restore part of the migration of my databases...

    A. M. Robinson

    Monday, July 6, 2015 3:45 PM
  • Sorry...haven't had a chance to reply back!

    I'll be giving both suggestions a try and will post my results shortly!


    A. M. Robinson

    Wednesday, July 8, 2015 8:33 PM
  • I've heard of people suggesting this and it doesn't work...

    A. M. Robinson

    Wednesday, July 22, 2015 8:16 PM
  • I took the SSIS route and that did the trick...

    I had some misgvings, but everything seems to work just fine!

    Thanks...!


    A. M. Robinson

    Wednesday, July 22, 2015 8:18 PM