locked
Migrating Sql agent jobs to a new server RRS feed

  • Question

  • We are migrating to a new upgraded hardware/software from Sql 2008 R2 to SQL 2017. What is the best, most efficient way to migrate sql agent jobs?

    Script out each job and runt the script on new server

    OR

    backup and restore system databases?

    Thanks.

    Monday, March 25, 2019 12:29 PM

Answers

  • In addition to method Olaf suggests, you can also script creation of all jobs from SSMS on source server by highlighting all jobs in Object Explorer Detail view, then right-clicking to display context menu:

    Then run the create script on the destination server where you wish to migrate jobs to.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Monday, March 25, 2019 1:09 PM
    • Marked as answer by kvd123 Monday, March 25, 2019 3:07 PM
    Monday, March 25, 2019 1:06 PM

All replies

  • In common it's not a good idea to restore "msdb" System database from a different Server. Better script the Job or use SSIS Transfer Jobs Task

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, March 25, 2019 12:43 PM
  • In addition to method Olaf suggests, you can also script creation of all jobs from SSMS on source server by highlighting all jobs in Object Explorer Detail view, then right-clicking to display context menu:

    Then run the create script on the destination server where you wish to migrate jobs to.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Monday, March 25, 2019 1:09 PM
    • Marked as answer by kvd123 Monday, March 25, 2019 3:07 PM
    Monday, March 25, 2019 1:06 PM