2

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

5
  • Why does it have to be TSQL only? PowerShell would make this a lot more workable and maintainable. May 8, 2016 at 12:34
  • It's to click in with an existing upgrade process that uses T-SQL scripts and SQLCMD. PowerShell isn't an option in this case. Although I would certainly be interested in looking into it for future.
    – Adam
    May 8, 2016 at 12:37
  • What is the purpose of copying it and also disabling it? It seems like redundant step. May 9, 2016 at 5:38
  • The job is being upgraded by another script (not shown here). The script required above is essentially a backup that is created prior to the upgrade script running and is compiled and ready to go (i.e. quick to re-enable) if there are any issues with the other script once it runs.
    – Adam
    May 9, 2016 at 8:13
  • 1
    @CodyKonior I have edited the question to accept Powershell as a solution as it seems the T-SQL method is not feasible.
    – Adam
    May 11, 2016 at 12:32

2 Answers 2

1
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%.
1

It would be a large script as a Job consists of data entries in multiple tables/views:

msdb.dbo.sysjobs_view
msdb.dbo.sysjobsteps
msdb.dbo.sysjobschedules
-- you probably dont need these as you are copying to the same server
msdb.dbo.sysschedules
msdb.dbo.syscategories
sys.server_principals
msdb.dbo.sysOperators

Create Jobs using following SPs:

msdb.dbo.sp_add_job
msdb.dbo.sp_add_jobserver
msdb.dbo.sp_add_jobstep
msdb.dbo.sp_add_jobschedule

Look in MSDN to work out what arguments to pass

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.