locked
How to move all SQL Server jobs and logins from one server to another? RRS feed

  • Question

  • Hi SQL Experts,

    I had been tasked with taking a backup of a SQL database and restoring it on a new SQL server I just setup.   Easy enough, I told them. But now, I've just found out that the development team needs all of the logins, services and jobs ported over as well.  Pretty much, a everything that SQL Server Agent and some of their special domain accounts handle.  Can someone point me to some good articles or in the right direction as to how to accomplish this?

    Thank you so much in advance!

    Saturday, March 25, 2017 2:42 AM

Answers

  • This is my favorite script for copying Logins/permissions from one SQL instance to another:
    Scripting Out the Logins, Server Role Assignments, and Server Permissions

    One easy way of scripting out Jobs is to highlight all of them at once in Object Explorer Details of SSMS, then > right-click > Script Job as > Create to:

    Then, you can create script to a file and run script in the new server instance to create jobs there.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Saturday, March 25, 2017 12:45 PM
    • Marked as answer by guesthost Saturday, March 25, 2017 1:32 PM
    • Unmarked as answer by guesthost Saturday, March 25, 2017 1:38 PM
    • Marked as answer by guesthost Monday, April 10, 2017 3:05 PM
    Saturday, March 25, 2017 12:43 PM

All replies

  • Hello,

    Is there any SSIS jobs ?are using any linked server ?

    Normal job can be copy using script.

    use sp_hexadecimal and sp_help_revlogin for login tranfer

    SSIS job--> 1)Package deployed in MSDB - right click and choose export package.you can can shave it locally as file and deploy to another server.

                        2) Local file you need copy and paste in another server.

    Note :- You need and change connection SSIS job after transfer  using BIDS.Check you are running using proxy account,then create proxy account also.


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.


    • Edited by AV111 Saturday, March 25, 2017 4:27 AM SSIS packages
    Saturday, March 25, 2017 4:16 AM
  • You've Transfer Jobs Task and Transfer Logins Task available in SSIS for this purpose


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, March 25, 2017 5:44 AM
  • This is my favorite script for copying Logins/permissions from one SQL instance to another:
    Scripting Out the Logins, Server Role Assignments, and Server Permissions

    One easy way of scripting out Jobs is to highlight all of them at once in Object Explorer Details of SSMS, then > right-click > Script Job as > Create to:

    Then, you can create script to a file and run script in the new server instance to create jobs there.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Saturday, March 25, 2017 12:45 PM
    • Marked as answer by guesthost Saturday, March 25, 2017 1:32 PM
    • Unmarked as answer by guesthost Saturday, March 25, 2017 1:38 PM
    • Marked as answer by guesthost Monday, April 10, 2017 3:05 PM
    Saturday, March 25, 2017 12:43 PM
  • This is ALL very exciting, but admittedly scary stuff! :)

    I'm a newbie, tasked with migrating a critical prod DB from a 2005 mirror with Mixed Authentication and a boatload of jobs, logins and permissions, to my new "simple" 2016 EE server that I setup with just Windows Authentication.

    The 2005 mirror has several users DBs that I will NOT be moving over, just one of them.  So I guess my first task is to determine which jobs, logins and associated roles/permissions belong to just that single database, correct?

    Thanks for helping me through these times experts - I don't know what I'd do without you!

    Saturday, March 25, 2017 1:47 PM