How to Create a Copy of a SQL Server Job

jobssql serversql-server-2008stored-procedurest-sql

Using T-SQL or Powershell, how can I create a stored procedure that performs the following:

  1. Copy a SQL Server Agent Job to another backup Job on the same server (with a new name)

  2. Disable the backup job so that it is not scheduled.

    USE msdb;

IF object_id('[backupSQLServerJob]') IS NULL EXEC ('create procedure [backupSQLServerJob] as select 1')
GO

ALTER PROCEDURE backupSQLServerJob
    @JobName varchar(50)
AS
BEGIN

    DECLARE @BackupName VARCHAR(100) = CONCAT(@JobName, '_bak_', CONVERT(VARCHAR(10), GETDATE(), 112));

    IF EXISTS (SELECT * FROM dbo.sysjobs WHERE name = @JobName)
    BEGIN
        -- What goes here?
    END
END

The solution needs to work on SQL Server 2008 – 2014

Best Answer

Import-Module SQLPS
$ErrorActionPreference = "Stop"
Set-StrictMode -Version Latest

$serverName = "VM-WIN81"
$instanceName = "SQL2014" # or DEFAULT
$jobName = "syspolicy_purge_history"
$jobSuffix = "_Old"

$script = Get-ChildItem SQLSERVER:\SQL\$serverName\$instanceName\JobServer\Jobs | Where { $_.Name -eq $jobName } | %{ 
    $_.Script() 
}
$script = $script -replace "@job_name=N'$jobName'", "@job_name=N'$jobName$jobSuffix'"
(Get-Item SQLSERVER:\SQL\$serverName\$instanceName).ConnectionContext.ExecuteNonQuery($script)
Get-ChildItem SQLSERVER:\SQL\$serverName\$instanceName\JobServer\Jobs | Where { $_.Name -eq "$jobName$jobSuffix" } | %{ 
    $_.Refresh()
    $_.IsEnabled = $false
    $_.Alter() 
}
  1. You can run this on servers with SQL 2008 R2 Feature Pack or SQL 2012. You can run it targeting remote servers.
  2. If the job name already exists, or there is any other error, it will break out.
  3. If the job name referred to itself in one of the steps or something that will of course be problematic seeing the simple replace I've done. You may be able to make this better with RegEx.
  4. When I do things like this I think about, "Is the new script going to modify the job schedule? Is this going to roll that back too?" You will need to test on one instance first and satisfy yourself it's 100%.
Related Question