locked
Database Jobs transfer RRS feed

  • Question

  • How do transfer Jobs from one SQL database to another? we are moving to a new server and I have scripted the jobs from the current server and would like to copy/import the jobs to the new server and database.

    Thanks,

    Craig

    Thursday, April 17, 2014 11:48 PM

Answers

  • Generate the create JOB T-SQL syntaxe like :

    • Proposed as answer by tracycai Friday, April 18, 2014 1:00 PM
    • Marked as answer by tracycai Saturday, April 26, 2014 7:36 AM
    Friday, April 18, 2014 12:13 AM
  • You can configure your requirement as per your choice like, you need to transfer all jobs or only specified jobs.

    This Technet Library would be more helpful to accomplish this task. Please go through :

    http://technet.microsoft.com/en-us/library/ms137568.aspx

    And, http://www.mssqltips.com/sqlservertip/2081/transfer-sql-server-jobs-task-and-transfer-sql-server-logins-task-in-ssis/

    • Proposed as answer by tracycai Friday, April 18, 2014 1:01 PM
    • Marked as answer by tracycai Saturday, April 26, 2014 7:36 AM
    Friday, April 18, 2014 6:55 AM
  • Hi,

    To move SQL jobs:

    1. Open BIDS and create a integration project, please pull the “Transfer Jobs Task” in to the Control Flow.

    2. Double click on the task, and connect to the old and the new server. Make the sure the connect is okay.

    3. You can choose to transfer all SQL jobs or select the jobs that you want to transfer in “JobList”.  Note: If the SQL jobs are related to the replication, it may failed.

    4. We can choose how to deal with the situation that the same job name is existing on the new server. Please choose the options as you want.

    See the below article for more information:

    Transfer SQL Server Jobs Task and Transfer SQL Server Logins Task in SSIS

    http://www.mssqltips.com/sqlservertip/2081/transfer-sql-server-jobs-task-and-transfer-sql-server-logins-task-in-ssis/

    Thanks.


    Tracy Cai
    TechNet Community Support

    • Proposed as answer by Prashanth Jayaram Friday, April 18, 2014 2:16 PM
    • Marked as answer by tracycai Saturday, April 26, 2014 7:36 AM
    Friday, April 18, 2014 1:00 PM
  • You can also use the Powershell to script the selected jobs in one file and execute the output file on the destination server.

    Change the selection criteria be giving a jobname(I've used 'Power*' )in the below code. If you want to select all the job then take out the below part from the script

     | Where-Object {$_.Name -like "POWER*"}  

    Execute the below script in Pwoershell-ISE.

    function script-SQLJobs([string]$server,[string]$outputfile) 
    { 
        [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null 
     
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server("$server") 
        $db = New-Object Microsoft.SqlServer.Management.Smo.Database 
        $scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv) 
        $scrp.Options.ScriptDrops = $FALSE 
        $scrp.Options.WithDependencies = $TRUE 
     
        $jobs = $srv.JobServer.get_Jobs() 
        $jobs=$jobs | Where-Object {$_.Name -like "POWER*"}     
     
        foreach($job in $jobs) 
        { 
            $script=$job.Script() 
            $script >> $outputfile 
            "GO" >> $outputfile 
     
        } 
    } 
    
    $server=Read-Host " Enter the server name"
    $OutputFile = Read-Host " Enter the Output File Path"
    
    script-SQLJobs $server $outputfile
    

    --Prashanth

    • Marked as answer by tracycai Saturday, April 26, 2014 7:36 AM
    Friday, April 18, 2014 2:16 PM

All replies

  • Generate the create JOB T-SQL syntaxe like :

    • Proposed as answer by tracycai Friday, April 18, 2014 1:00 PM
    • Marked as answer by tracycai Saturday, April 26, 2014 7:36 AM
    Friday, April 18, 2014 12:13 AM
  • You can configure your requirement as per your choice like, you need to transfer all jobs or only specified jobs.

    This Technet Library would be more helpful to accomplish this task. Please go through :

    http://technet.microsoft.com/en-us/library/ms137568.aspx

    And, http://www.mssqltips.com/sqlservertip/2081/transfer-sql-server-jobs-task-and-transfer-sql-server-logins-task-in-ssis/

    • Proposed as answer by tracycai Friday, April 18, 2014 1:01 PM
    • Marked as answer by tracycai Saturday, April 26, 2014 7:36 AM
    Friday, April 18, 2014 6:55 AM
  • Hi,

    To move SQL jobs:

    1. Open BIDS and create a integration project, please pull the “Transfer Jobs Task” in to the Control Flow.

    2. Double click on the task, and connect to the old and the new server. Make the sure the connect is okay.

    3. You can choose to transfer all SQL jobs or select the jobs that you want to transfer in “JobList”.  Note: If the SQL jobs are related to the replication, it may failed.

    4. We can choose how to deal with the situation that the same job name is existing on the new server. Please choose the options as you want.

    See the below article for more information:

    Transfer SQL Server Jobs Task and Transfer SQL Server Logins Task in SSIS

    http://www.mssqltips.com/sqlservertip/2081/transfer-sql-server-jobs-task-and-transfer-sql-server-logins-task-in-ssis/

    Thanks.


    Tracy Cai
    TechNet Community Support

    • Proposed as answer by Prashanth Jayaram Friday, April 18, 2014 2:16 PM
    • Marked as answer by tracycai Saturday, April 26, 2014 7:36 AM
    Friday, April 18, 2014 1:00 PM
  • You can also use the Powershell to script the selected jobs in one file and execute the output file on the destination server.

    Change the selection criteria be giving a jobname(I've used 'Power*' )in the below code. If you want to select all the job then take out the below part from the script

     | Where-Object {$_.Name -like "POWER*"}  

    Execute the below script in Pwoershell-ISE.

    function script-SQLJobs([string]$server,[string]$outputfile) 
    { 
        [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null 
     
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server("$server") 
        $db = New-Object Microsoft.SqlServer.Management.Smo.Database 
        $scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv) 
        $scrp.Options.ScriptDrops = $FALSE 
        $scrp.Options.WithDependencies = $TRUE 
     
        $jobs = $srv.JobServer.get_Jobs() 
        $jobs=$jobs | Where-Object {$_.Name -like "POWER*"}     
     
        foreach($job in $jobs) 
        { 
            $script=$job.Script() 
            $script >> $outputfile 
            "GO" >> $outputfile 
     
        } 
    } 
    
    $server=Read-Host " Enter the server name"
    $OutputFile = Read-Host " Enter the Output File Path"
    
    script-SQLJobs $server $outputfile
    

    --Prashanth

    • Marked as answer by tracycai Saturday, April 26, 2014 7:36 AM
    Friday, April 18, 2014 2:16 PM