none
Cancel jobs in DPM 2010 through PowerShell

    Question

  • Could someone please provide the necessary commands to cancel jobs for a particular PG (for example named PG1).

    I've used the help, TechNet, but am unable to find something like that. I wouldn't even need them if I was able to change the Synchronization frequency to more then 24h.

    Thursday, November 03, 2011 1:31 PM

Answers

  • Hi CypherMike,

    Here is the version 1 of of the script. Let me know if you face any problems.

    Just create a ps1 file on your DPM Bin directory and paste the code below on it.  I call this script canceljobs.ps1

    ################################################################################################
    #                                                                                              #
    # This script will cancel all jobs from all protection groups between the given hours          #
    # Scheduled jobs that are not bound to a protection group won't be cancelled.                  #
    # i.e. maintenance jobs like Tape Inventory, etc                                               #
    #                                                                                              #
    # If you used the wrong time interval and ended up cancelled undesired scheduled jobs          #
    # Just go through modify the protection group wizard without making any changes and the        #
    # cancelled jobs will show up again.                                                           #
    #                                                                                              #
    # This script uses SQL Cmdlet so in order for the script to be successful the user should      #
    # have access to SQL Database. This version of the script expects SQL to be installed on  #
    # the same machine that DPM was installed and using the DPM Default instance name (MSDPM2010)  #
    # You can enter the time in 24h or 12h format. Example: 17:00 = 5:00PM                         #
    #                                                                                              #
    # Note: You can run this script multiple times on the same day without bloating the DPM        #
    #       Database as the script checks if a job was already cancelled before adding a new entry #
    #                                                                                              #
    # Example: .\Canceljobs.ps1 2:01 17:59                                                         #
    #          .\Canceljobs.ps1 2:01AM 5:59PM                                                      #
    #                                                                                              #
    # Both syntaxes above will cancel all jobs scheduled to run from 2:01 AM to 5:59PM  #
    #                                                                                              #
    # Author     : Wilson Souza                                                                    #
    # Date       : 11/15/2011                                                                      #
    # Version    : 1.0                                                                             #
    #                                                                                              #
    # This version of the script was only tested on DPM 2010                                       #
    #                                                                                              #
    ################################################################################################
    
    param([datetime] $Start, [Datetime] $End)
    
    add-pssnapin sqlservercmdletsnapin100
    cls
    
    if (!$start) { [datetime]$Start = read-host "Cancel jobs - starting time (hh:mm)" }
    if (!$End)   { [datetime]$End   = read-host "Cancel jobs - End time (hh:mm)"      }
    
    $ConfirmPreference = 'None'
    
    $query = "use DPMDB
    select distinct def.JobDefinitionId
    	,friendlyname
    from    tbl_SCH_ScheduleDefinition sch 
           ,msdb.dbo.sysjobs jobs
           ,tbl_JM_JobDefinition def
           ,DPMDB.dbo.tbl_IM_ProtectedGroup prot
           ,msdb.dbo.sysjobschedules jobsch
           ,msdb.dbo.sysjobsteps jobsteps
           ,msdb.dbo.sysschedules syssch
    where CAST(sch.ScheduleId as NCHAR (128)) = jobs.name
    and def.JobDefinitionId = sch.JobDefinitionId
    and def.ProtectedGroupId = prot.ProtectedGroupId
    and jobs.job_id = jobsch.job_id
    and jobs.job_id = jobsteps.job_id
    and jobsch.schedule_id = syssch.schedule_id
    and sch.IsDeleted = '0' and def.ProtectedGroupId is not null
    and def.type <> 'C9B259D2-6402-486D-8E36-C6C1ADAE0912'
    go" 
    
    
    $currentday = get-date
    $result = Invoke-Sqlcmd -query $query  -serverinstance '.\msdpm2010'
    
    foreach ($result1 in $result)
    {
    	$query1 = "select ScheduleXml from dpmdb.dbo.tbl_sch_scheduledefinition where jobdefinitionid = '"+ $result1.jobdefinitionid + "' and IsDeleted = '0'" 
    	$schedule = invoke-sqlcmd -query $query1 -serverinstance ".\msdpm2010"
    	[xml]$xmlschedule = $schedule.schedulexml
    	if ($xmlschedule.Schedule.FirstChild.daily)
    	{
    		[datetime]$StartDate = $xmlschedule.Schedule.FirstChild.daily.StartDt
    		if ($startdate -le $currentday)
    		{
    			if ($xmlschedule.Schedule.FirstChild.MinuteRecurrence.starttm)
    			{
    				[datetime]$hoursSt = $xmlschedule.Schedule.FirstChild.MinuteRecurrence.starttm
    				while ($HoursSt -lt $start) {$hoursSt = $hoursSt.addminutes($xmlschedule.Schedule.FirstChild.MinuteRecurrence.interval)}
    				while ($HoursSt -ge $Start -and $hoursSt -le $end -and $hoursSt.day -eq $currentday.day)
    				{
    					$hoursEd = $hoursSt.addminutes(5)
    					$update = "if not exists (select * from dpmdb.dbo.tbl_sch_schedulecancellation where jobdefinitionid = '" + 
    
    $result1.jobdefinitionid + "' and StartDateTime = '" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') insert into dpmdb.dbo.tbl_sch_schedulecancellation values 
    
    ( newid()" + ",'" + $result1.jobdefinitionid + "','" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "','" + $hoursEd.ToString("yyyy-MM-dd HH:mm:ss.fff") + "')"
    					invoke-sqlcmd -query $update -serverinstance '.\msdpm2010'
    					$hoursSt = $hoursSt.addminutes($xmlschedule.Schedule.FirstChild.MinuteRecurrence.interval)
    					$hoursEd = $hoursSt.addminutes(5)
    				}
    			}
    		}
    	}
    
       
    	if ($xmlschedule.Schedule.FirstChild.weekly)
    	{
    		$count = 0
    		while ($count -lt $xmlschedule.schedule.Recurrence.count -or $xmlschedule.schedule.Recurrence.count -eq $Null )
    		{
    			if (!$xmlschedule.schedule.Recurrence[$count])
    			{
    				[datetime]$StartDate = $xmlschedule.schedule.Recurrence.weekly.startdt
    				[datetime]$StartDate = $xmlschedule.schedule.Recurrence.weekly.startdt
    				if ($startdate -le $currentday)
    				{
    					if ($xmlschedule.Schedule.recurrence.weekly.weekdaylist -match ($currentday.DayOfWeek.ToString()).substring(0,2))
    					{
    						[datetime]$hoursSt = $xmlschedule.Schedule.recurrence.time.StartTm
    						if ($hoursSt -ge $start -and $HoursSt -le $end)
    						{
    							$hoursEd = $hoursSt.addminutes(5)
    							$update = "if not exists (select * from dpmdb.dbo.tbl_sch_schedulecancellation where jobdefinitionid = '" + 
    
    $result1.jobdefinitionid + "' and StartDateTime = '" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') insert into dpmdb.dbo.tbl_sch_schedulecancellation values 
    
    ( newid()" + ",'" + $result1.jobdefinitionid + "','" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "','" + $hoursEd.ToString("yyyy-MM-dd HH:mm:ss.fff") + "')"
    							invoke-sqlcmd -query $update -serverinstance '.\msdpm2010'
    						}
    					}
    				}
    				break
    			}
    			else
    			{
    				[datetime]$StartDate = $xmlschedule.schedule.Recurrence[$count].weekly.startdt
    				[datetime]$StartDate = $xmlschedule.schedule.Recurrence[$count].weekly.startdt
    				if ($startdate -le $currentday)
    				{
    					if ($xmlschedule.Schedule.recurrence[$count].weekly.weekdaylist -match ($currentday.DayOfWeek.ToString()).substring(0,2))
    					{
    						[datetime]$hoursSt = $xmlschedule.Schedule.recurrence[$count].time.StartTm
    						if ($hoursSt -ge $start -and $HoursSt -le $end)
    						{						
    							$hoursEd = $hoursSt.addminutes(5)
    							$update = "if not exists (select * from dpmdb.dbo.tbl_sch_schedulecancellation where jobdefinitionid = '" + 
    
    $result1.jobdefinitionid + "' and StartDateTime = '" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') insert into dpmdb.dbo.tbl_sch_schedulecancellation values 
    
    ( newid()" + ",'" + $result1.jobdefinitionid + "','" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "','" + $hoursEd.ToString("yyyy-MM-dd HH:mm:ss.fff") + "')"
    							invoke-sqlcmd -query $update -serverinstance '.\msdpm2010'
    						}
    					}
    				}
    				$count++
    			}
    		}
    	}
    }
    
    

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights

    Wednesday, November 16, 2011 6:29 AM

All replies

  • The goal is to cancel a job that is scheduled to run and not actually running, correct?
    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, November 04, 2011 5:02 AM
  • Yes, I don't want to sync from my primary to my secondary each day.
    Friday, November 04, 2011 5:24 AM
  • DPM doesn't have build in command to that... so let me see if I can create something out to achieve this.....
    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, November 04, 2011 5:25 AM
  • Wilson have you by any chance managed to come up with something. I'll very shortly need this functionality.
    Monday, November 14, 2011 11:26 AM
  • I am about half way...

    As there is no DPM PoweShell cmdlet for that I am using SQL PowerShell cmdlet as well....


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, November 15, 2011 8:20 AM
  • Wow, thank you so much. I'm very much looking forward to it.

    Tuesday, November 15, 2011 8:31 AM
  • Hi CypherMike,

    Here is the version 1 of of the script. Let me know if you face any problems.

    Just create a ps1 file on your DPM Bin directory and paste the code below on it.  I call this script canceljobs.ps1

    ################################################################################################
    #                                                                                              #
    # This script will cancel all jobs from all protection groups between the given hours          #
    # Scheduled jobs that are not bound to a protection group won't be cancelled.                  #
    # i.e. maintenance jobs like Tape Inventory, etc                                               #
    #                                                                                              #
    # If you used the wrong time interval and ended up cancelled undesired scheduled jobs          #
    # Just go through modify the protection group wizard without making any changes and the        #
    # cancelled jobs will show up again.                                                           #
    #                                                                                              #
    # This script uses SQL Cmdlet so in order for the script to be successful the user should      #
    # have access to SQL Database. This version of the script expects SQL to be installed on  #
    # the same machine that DPM was installed and using the DPM Default instance name (MSDPM2010)  #
    # You can enter the time in 24h or 12h format. Example: 17:00 = 5:00PM                         #
    #                                                                                              #
    # Note: You can run this script multiple times on the same day without bloating the DPM        #
    #       Database as the script checks if a job was already cancelled before adding a new entry #
    #                                                                                              #
    # Example: .\Canceljobs.ps1 2:01 17:59                                                         #
    #          .\Canceljobs.ps1 2:01AM 5:59PM                                                      #
    #                                                                                              #
    # Both syntaxes above will cancel all jobs scheduled to run from 2:01 AM to 5:59PM  #
    #                                                                                              #
    # Author     : Wilson Souza                                                                    #
    # Date       : 11/15/2011                                                                      #
    # Version    : 1.0                                                                             #
    #                                                                                              #
    # This version of the script was only tested on DPM 2010                                       #
    #                                                                                              #
    ################################################################################################
    
    param([datetime] $Start, [Datetime] $End)
    
    add-pssnapin sqlservercmdletsnapin100
    cls
    
    if (!$start) { [datetime]$Start = read-host "Cancel jobs - starting time (hh:mm)" }
    if (!$End)   { [datetime]$End   = read-host "Cancel jobs - End time (hh:mm)"      }
    
    $ConfirmPreference = 'None'
    
    $query = "use DPMDB
    select distinct def.JobDefinitionId
    	,friendlyname
    from    tbl_SCH_ScheduleDefinition sch 
           ,msdb.dbo.sysjobs jobs
           ,tbl_JM_JobDefinition def
           ,DPMDB.dbo.tbl_IM_ProtectedGroup prot
           ,msdb.dbo.sysjobschedules jobsch
           ,msdb.dbo.sysjobsteps jobsteps
           ,msdb.dbo.sysschedules syssch
    where CAST(sch.ScheduleId as NCHAR (128)) = jobs.name
    and def.JobDefinitionId = sch.JobDefinitionId
    and def.ProtectedGroupId = prot.ProtectedGroupId
    and jobs.job_id = jobsch.job_id
    and jobs.job_id = jobsteps.job_id
    and jobsch.schedule_id = syssch.schedule_id
    and sch.IsDeleted = '0' and def.ProtectedGroupId is not null
    and def.type <> 'C9B259D2-6402-486D-8E36-C6C1ADAE0912'
    go" 
    
    
    $currentday = get-date
    $result = Invoke-Sqlcmd -query $query  -serverinstance '.\msdpm2010'
    
    foreach ($result1 in $result)
    {
    	$query1 = "select ScheduleXml from dpmdb.dbo.tbl_sch_scheduledefinition where jobdefinitionid = '"+ $result1.jobdefinitionid + "' and IsDeleted = '0'" 
    	$schedule = invoke-sqlcmd -query $query1 -serverinstance ".\msdpm2010"
    	[xml]$xmlschedule = $schedule.schedulexml
    	if ($xmlschedule.Schedule.FirstChild.daily)
    	{
    		[datetime]$StartDate = $xmlschedule.Schedule.FirstChild.daily.StartDt
    		if ($startdate -le $currentday)
    		{
    			if ($xmlschedule.Schedule.FirstChild.MinuteRecurrence.starttm)
    			{
    				[datetime]$hoursSt = $xmlschedule.Schedule.FirstChild.MinuteRecurrence.starttm
    				while ($HoursSt -lt $start) {$hoursSt = $hoursSt.addminutes($xmlschedule.Schedule.FirstChild.MinuteRecurrence.interval)}
    				while ($HoursSt -ge $Start -and $hoursSt -le $end -and $hoursSt.day -eq $currentday.day)
    				{
    					$hoursEd = $hoursSt.addminutes(5)
    					$update = "if not exists (select * from dpmdb.dbo.tbl_sch_schedulecancellation where jobdefinitionid = '" + 
    
    $result1.jobdefinitionid + "' and StartDateTime = '" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') insert into dpmdb.dbo.tbl_sch_schedulecancellation values 
    
    ( newid()" + ",'" + $result1.jobdefinitionid + "','" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "','" + $hoursEd.ToString("yyyy-MM-dd HH:mm:ss.fff") + "')"
    					invoke-sqlcmd -query $update -serverinstance '.\msdpm2010'
    					$hoursSt = $hoursSt.addminutes($xmlschedule.Schedule.FirstChild.MinuteRecurrence.interval)
    					$hoursEd = $hoursSt.addminutes(5)
    				}
    			}
    		}
    	}
    
       
    	if ($xmlschedule.Schedule.FirstChild.weekly)
    	{
    		$count = 0
    		while ($count -lt $xmlschedule.schedule.Recurrence.count -or $xmlschedule.schedule.Recurrence.count -eq $Null )
    		{
    			if (!$xmlschedule.schedule.Recurrence[$count])
    			{
    				[datetime]$StartDate = $xmlschedule.schedule.Recurrence.weekly.startdt
    				[datetime]$StartDate = $xmlschedule.schedule.Recurrence.weekly.startdt
    				if ($startdate -le $currentday)
    				{
    					if ($xmlschedule.Schedule.recurrence.weekly.weekdaylist -match ($currentday.DayOfWeek.ToString()).substring(0,2))
    					{
    						[datetime]$hoursSt = $xmlschedule.Schedule.recurrence.time.StartTm
    						if ($hoursSt -ge $start -and $HoursSt -le $end)
    						{
    							$hoursEd = $hoursSt.addminutes(5)
    							$update = "if not exists (select * from dpmdb.dbo.tbl_sch_schedulecancellation where jobdefinitionid = '" + 
    
    $result1.jobdefinitionid + "' and StartDateTime = '" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') insert into dpmdb.dbo.tbl_sch_schedulecancellation values 
    
    ( newid()" + ",'" + $result1.jobdefinitionid + "','" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "','" + $hoursEd.ToString("yyyy-MM-dd HH:mm:ss.fff") + "')"
    							invoke-sqlcmd -query $update -serverinstance '.\msdpm2010'
    						}
    					}
    				}
    				break
    			}
    			else
    			{
    				[datetime]$StartDate = $xmlschedule.schedule.Recurrence[$count].weekly.startdt
    				[datetime]$StartDate = $xmlschedule.schedule.Recurrence[$count].weekly.startdt
    				if ($startdate -le $currentday)
    				{
    					if ($xmlschedule.Schedule.recurrence[$count].weekly.weekdaylist -match ($currentday.DayOfWeek.ToString()).substring(0,2))
    					{
    						[datetime]$hoursSt = $xmlschedule.Schedule.recurrence[$count].time.StartTm
    						if ($hoursSt -ge $start -and $HoursSt -le $end)
    						{						
    							$hoursEd = $hoursSt.addminutes(5)
    							$update = "if not exists (select * from dpmdb.dbo.tbl_sch_schedulecancellation where jobdefinitionid = '" + 
    
    $result1.jobdefinitionid + "' and StartDateTime = '" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "') insert into dpmdb.dbo.tbl_sch_schedulecancellation values 
    
    ( newid()" + ",'" + $result1.jobdefinitionid + "','" + $hoursSt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "','" + $hoursEd.ToString("yyyy-MM-dd HH:mm:ss.fff") + "')"
    							invoke-sqlcmd -query $update -serverinstance '.\msdpm2010'
    						}
    					}
    				}
    				$count++
    			}
    		}
    	}
    }
    
    

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights

    Wednesday, November 16, 2011 6:29 AM
  • Thank you so much Wilson.

    I just copied it over to my DPM server, tried to run it, but it didn't do anything (there appeared to be an error, but it went by so quickly that it was impossible to see it, after that the Shell windows was cleared), which makes sense since there are no more jobs scheduled for today (I run them at 0:15). I'll create a scheduled task, test and report back.

    Wednesday, November 16, 2011 8:49 AM
  • Hi there,

     

    There is an error that shows up at the beginning (and cleared up by CLS command) of the script and this is expected as when we are loading the SQL Cmdlets there is a file which was already added by DPM Shell and this is what the error is about....

    There is no other output after you enter the start and end time.  Once you get the prompt back the script is over.

    Next step is to go check in DPM UI (Monitoring/Jobs) if all jobs that were within the start/end time were gone...


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Wednesday, November 16, 2011 8:53 AM
  • OK, thank you for the explanation.

    I went to DPM UI (Monitoring/Jobs), but nothing has changed, which makes sense, since no jobs are scheduled for today. The ones I want to cancel on most days of the week are all scheduled for 0:15 and should complete at about 01:30.

    I created a scheduled task that runs as 0:00 and executes .\Canceljobs.ps1 00:10 01:50. The other PG, that needs to run on a daily basis starts at 03:00.

    Wednesday, November 16, 2011 9:05 AM
  • Hi Wilson,

    could you please help me out a bit more. Although the scheduled task runs just fine nothing gets cancelled.
    I have a batch file CancelJob.bat, that is executed on a daily basis, which looks like this:

    C:\Windows\system32\windowspowershell\v1.0\powershell.exe -PSConsoleFile "C:\Program Files\Microsoft DPM\DPM\bin\dpmshell.psc1" -command ".'C:\Program Files\Microsoft DPM\DPM\bin\dpmcliinitscript.ps1';C:\Tasks\CancelJobs\CancelJobs.ps1 0:05 1:50"

    As mentioned the jobs I want to cancel start at 00:15 and always end before 01:30.

     

    Monday, November 21, 2011 10:14 AM
  • What if you modify a protection group to run a backup in the afternoon?

    Then run the script manually and see if that job cancelled. If so, do the same thing but with the script running as scheduled tast.

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Monday, November 21, 2011 7:58 PM
  • I did as suggested, but I'm unable to cancel it manually. I tried all different variations:

    -  .\canceljobs.ps1 08:00 10:00
    -  .\canceljobs.ps1 8:00 10:00
    -  .\canceljobs.ps1 8:00AM 10:00AM
    -  .\canceljobs.ps1 and then entering the times when prompted.

    If I go to Monitoring\Jobs\ the jobs are still showing up as scheduled.

    Tuesday, November 22, 2011 6:45 AM
  • Thank you for the update.

    Which type of backups you have on your secondary DPM server (like Hyper-V, Volumes, Exchange, etc).


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, November 22, 2011 6:53 AM
  • The PG's jobs I want to cancel include Hyper-V and System Protection (BMR):

    Tuesday, November 22, 2011 7:04 AM
  • I'll enable secondary protection and see if I can repro this.


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, November 22, 2011 7:05 AM
  • Ok,

    Just tested the script on my secondary DPM server with Hyper-V Protection.

    Here is the original scheduled jobs

    Here is the output after running the script

     

    Does the user you are running the script have permissions to connect to the DPM SQL instance?
    Is the DPM SQL instance installed locally on the secondary DPM server and it is using its default name (MSDPM2010)?

     

     

     

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, November 25, 2011 6:43 AM
  • How do I check this?

    I've used the defaults when installing and have the password handy, but am unsure about the username.

    Friday, November 25, 2011 2:00 PM
  • Open SQL Server Management Studio with the account you are using to run the script and see if you can connect to DPM SQL instance with it.

    Or you can also open SQL Shell (C:\Program Files\Microsoft DPM\SQL (x86)\100\Tools\Binn\SQLPS.exe) and run the following command.

     

    Invoke-Sqlcmd -query "select @@version go"  -serverinstance '.\msdpm2010'
    


    The output should be something like this

     

    go
    --
    Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) ...


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights

    Friday, November 25, 2011 7:41 PM
  • I get:

    PS SQLSERVER:\> Invoke-Sqlcmd -query "select @@version go"  -serverinstance '.\m
    sdpm2010'

    go
    --
    Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (X64) ...

     

    BTW is SP2 officially supported with DPM 2010?

    Saturday, November 26, 2011 11:22 AM
  • Hi CypherMike,

    Did you manage to make it work?

    Yes, we do support SQL 2008 SP2


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, January 13, 2012 8:47 AM