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. – Cody Konior May 8 '16 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 '16 at 12:37
  • What is the purpose of copying it and also disabling it? It seems like redundant step. – Sir Swears-a-lot May 9 '16 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 '16 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 '16 at 12:32
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, privacy policy and cookie policy

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