Monthly and Yearly backups not working for Protection Group?

Hi

We have a Protection Group with 160 members currently attached to it. The group has the following customized Recovery Points:-

- 1 recovery point every 1 day for the last 2 weeks, 1 recovery point every 1 month for the last 60 months and 1 recovery point every year for the last 5 years.

The schedule is as follows:-

- Every Day at 01:00, Every Month on First Day at 01:00 and Every 12 Months on 01 January at 01:00.

The daily tape backups are working fine without issue but neither the Monthly or Yearly backups are being attempted. We have checked the monthly backups for this issue for the last few months and are unable to see any evidence of the Monthly schedule even running? This means that we are having to manually label the Daily tapes once a month as Monthly tapes but they will then stay on the Reports for the next 5 years as DPM will constantly request these tapes back.

Are you able to help with this matter?

Not that we really want to do this but if we removed the group completely and re-create it would this cause problems with our restore points?

May 21st, 2012 2:33pm

Hi,

This is a known issue that can occur in all current versions of DPM including DPM 2012, to correct the condition, you simply need to perform this step. Any time before the next scheduled (monthly or longer) backup, manually update the long term backup schedule by hitting the MODIFY button.  The code that runs when modifying the backup schedule redoes the bacup schedules and it fixes the scheduling issue. 

 

Below is a DPM Power-shell script that will show you the scheduled backups for all recovery goals for all protection groups.  You can see the last time each recovery goal ran, and the NEXT run time, so you can monitor the goals and re-fix them if necessary.

NOTE: It takes about 20-30 minutes before the fixed next run time schedule will be populated after modify the protection group and updating the schedule.

#                                                                                              
# This script will list all currently scheduled backup to tape jobs                            
# Then you can select from that list which backup to tape job you want to run                  
#                                                                                              
# Author        : Wilson Souza                                                                 
# Date Created  : 07/13/2012                                                                   
# Last modified : 01/21/2013
# Version       : 1.6                                                                          
#                                                                                              
# Change log                                                                                   
# ==========                                                                                   
#                           
#       Ver 1.6 - Tested script with DPM 2012/DPM2012SP1/DPM2012R2                                                                   
#                 Fixed issue when DPM database is running within a SQL Default Instance
#       Ver 1.5 - Changed text related to where you can monitor the job after triggered        
#       Ver 1.4 - Found an issue for variable $Result when only a single row is returned       
#       Ver 1.3 - Added PowerShell variable to hide snap-in load error                         
#                 Script query registry to check where DPM database is located                 
#       Ver 1.2 - Added support for Copy tape configuration (up to 7 copies)                   
#       Ver 1.1 - Added Verbose switch to show more output information                         
#		          Added Short Term/Long Term Information                                       
#	              Added Rocovery goal information                                              
#                                                                                              
#                              
#                                                                                              

param([string] $verbose)
$ErrorActionPreference = "silentlycontinue"
add-pssnapin sqlservercmdletsnapin100
Add-PSSnapin -Name Microsoft.DataProtectionManager.PowerShell
$ConfirmPreference = 'None'
cls
$instance = Get-itemproperty "hklm:\SOFTWARE\Microsoft\Microsoft Data Protection Manager\DB\"
$dpmdb = $instance.databasename

if ($instance.instancename -eq 'MSSQLSERVER')
{
    $instance = $instance.SqlServer
}
else
{
   $instance = $instance.SqlServer + '\' + $instance.instancename
}


$query = "CREATE FUNCTION label (@GUID varchar(36), @kindred varchar(4), @vault varchar(8))
returns varchar (1024)
as
Begin
   declare @result varchar (1024)
   select @result = vaUltlabel from tbl_mm_vaultlabel where mediapoolid = @GUID and generation = 
      case  @kindred
          when 'Fath' Then '2'
          when 'Gran' then '1'
          when 'grea' Then '0'
      end and
      vault =
      case @vault
		  when 'Offsite1' then '3'
		  when 'Offsite2' then '4'
		  when 'Offsite3' then '5'
		  when 'Offsite4' then '6'
		  when 'Offsite5' then '7'
		  when 'Offsite6' then '8'
		  when 'Offsite7' then '9'
	  else
	      '1'
	  end
   RETURN @result
END
go
 
select ScheduleId as name
       ,def.JobDefinitionId as JD
       ,FriendlyName as PG
       ,SUBSTRING (CONVERT(VARCHAR(10),active_start_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),active_start_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),active_start_date),1,4) as SD
       ,jobs.date_created as SCD
       ,SUBSTRING (CONVERT(VARCHAR(10),last_run_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),last_run_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),last_run_date),1,4) + '  ' +
        SUBSTRING (CONVERT(VARCHAR(6),last_run_time),1,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),last_run_time),3,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),last_run_time),5,2) as LRD
       ,SUBSTRING (CONVERT(VARCHAR(10),next_run_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),next_run_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),next_run_date),1,4) + '  ' +
        SUBSTRING (CONVERT(VARCHAR(6),next_run_time),1,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),next_run_time),3,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),next_run_time),5,2) as NRD
       ,dbo.label ((substring(xml,(patindex('%MediaPoolId%',Xml))+13,36)), (substring(xml,(patindex('%generation%',Xml))+12,4)), (substring(xml,(patindex('%vault%',Xml))+7,8))) as TL
       ,case 
			when substring(xml,(patindex('%vault%',Xml))+7,3) = 'off'  then 'Long-Term' 
			else 'Short-term'
       end as STLT
       ,case
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Fath' then 'Recovery Goal 1'
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Gran' then 'Recovery Goal 2'
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Grea' then 'Recovery Goal 3'
	end as RG
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 (def.Type = '913afd2d-ed74-47bd-b7ea-d42055e5c2f1' or def.Type = 'B5A3D25C-8EB2-4032-9428-C852DA5CE2C5')
and sch.IsDeleted = '0' and def.ProtectedGroupId is not null
order by FriendlyName, next_run_date, next_run_time
go
 
drop function label
go"

[array]$result = Invoke-Sqlcmd -ServerInstance $instance -Query $query -Database $dpmdb
$count = 1
write-host " The list below shows all scheduled backup to tape jobs (short term and long term)" -f green
write-host

if ($verbose.ToLower() -eq 'verbose')
{
	write-host " For optimun output, set PoweShell Width for screen buffer size to at least 300" -f yellow; write-host
	write-host
	write-host "     Protection Group               SQL Agent Name                       JobDefinitionID                      Creation Date Schedule Creation Date Last Run Date        Next Sched Run Date  Term       Goal            Tape Label"
	write-host "     ------------------------------ ------------------------------------ ------------------------------------ ------------- ---------------------- -------------------- -------------------- ---------- --------------- --------------" 
	foreach ($result1 in $result)
	{
		if ($color -eq 'white') {$color = 'cyan'} else {$color = 'white'}
		write-host ("{0,2}"-f $count) -foreground green -nonewline
		write-host ( " - {0,-30} {1,36} {2,36} {3,-13} {4,-22} {5,-20} {6,-20} {7,-10} {8,15} " -f $result1.PG, $result1.name, $result1.jd, $result1.SD, $result1.SCD, $result1.LRD, $result1.NRD, $result1.STLT, $result1.RG) -nonewline -f $color
		write-host $result1.TL -f yellow
		$count++
	}
}
else
{
	write-host " For optimun output, set PoweShell Width for screen buffer size  to at least 110" -f yellow; write-host
	write-host "     Protection Group               Term       Goal            Tape Label"
	write-host "     ------------------------------ ---------- --------------- --------------" 
	foreach ($result1 in $result)
	{
		if ($color -eq 'white') {$color = 'cyan'} else {$color = 'white'}
		write-host ("{0,2}"-f $count) -foreground green -nonewline
		write-host ( " - {0,-30} {1,-10} {2,15} " -f $result1.PG, $result1.STLT, $result1.RG) -nonewline -f $color
		write-host $result1.TL -f yellow
		$count++
	}
}

write-host
write-host "Which job(s) you want to run? If running more than one job enter numbers separated by space: " -f green -nonewline
$runjob = read-host
$runjob = $runjob -split " "
$executingjob = 0
if ($runjob)
{
	foreach ($startjob in $runjob)
	{
		$firejob = [int]$startjob
		if ($firejob -gt 0 -and $firejob -lt $count)
		{
			$query = "EXEC msdb.dbo.sp_start_job '{0}'" -f $result[$firejob-1].name
			Invoke-Sqlcmd -ServerInstance $instance -Query $query -Database $dpmdb
			$executingjob++
		}
	}
}
write-host
if ($executingjob -gt 0)
{
	write-host "You selected to run $executingjob job(s). You can monitor job(s) progress via DPM Administrator Console" -f green
}
else
{
 	write-host "Due to the selection entered, no jobs will run" -f red
}

Free Windows Admin Tool Kit Click here and download it now
May 30th, 2012 3:11am

Hi,

This is a known issue that can occur in all current versions of DPM including DPM 2012, to correct the condition, you simply need to perform this step. Any time before the next scheduled (monthly or longer) backup, manually update the long term backup schedule by hitting the MODIFY button.  The code that runs when modifying the backup schedule redoes the bacup schedules and it fixes the scheduling issue. 

 

Below is a DPM Power-shell script that will show you the scheduled backups for all recovery goals for all protection groups.  You can see the last time each recovery goal ran, and the NEXT run time, so you can monitor the goals and re-fix them if necessary.

NOTE: It takes about 20-30 minutes before the fixed next run time schedule will be populated after modify the protection group and updating the schedule.

################################################################################################
#                                                                                              #
# This script will list all currently scheduled backup to tape jobs                            #
# It will list scheduled, last run and next run dates                                          #
#                                                                                              #
# Note: The script takes in consideration that the DPM Database was installed locally on its   #
#       Default instance. If SQL is installed on a different location/instance, edit the line  #
#       that starts with $instance = '.\msdpm2010                                              #
#                                                                                              #
# Author        : Wilson Souza                                                                 #
# Date Created  : 1/13/2012                                                                    #
# Last modified : 1/17/2012                                                                    #
# Version       : 1.0                                                                          #
#                                                                                              #
#                                                                                              #
# This version of the script was only tested on DPM 2010                                       #
#                                                                                              #
################################################################################################

param([string] $verbose)
add-pssnapin sqlservercmdletsnapin100
Add-PSSnapin -Name Microsoft.DataProtectionManager.PowerShell
$ConfirmPreference = 'None'
cls
$instance = '.\msdpm2010'  # <---- If DPM Database is on a different location, edit this line accordinly
$query = "use DPMDB
go
CREATE FUNCTION label (@GUID varchar(36), @kindred varchar(4), @vault varchar(8))
returns varchar (1024)
as
Begin
   declare @result varchar (1024)
   select @result = vaUltlabel from tbl_mm_vaultlabel where mediapoolid = @GUID and generation = 
      case  @kindred
          when 'Fath' Then '2'
          when 'Gran' then '1'
          when 'grea' Then '0'
      end and
      vault =
      case @vault
		  when 'Offsite1' then '3'
		  when 'Offsite2' then '4'
		  when 'Offsite3' then '5'
		  when 'Offsite4' then '6'
		  when 'Offsite5' then '7'
		  when 'Offsite6' then '8'
		  when 'Offsite7' then '9'
	  else
	      '1'
	  end
   RETURN @result
END
go
 
use DPMDB
select ScheduleId as name
       ,def.JobDefinitionId as JD
       ,FriendlyName as PG
       ,SUBSTRING (CONVERT(VARCHAR(10),active_start_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),active_start_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),active_start_date),1,4) as SD
       ,jobs.date_created as SCD
       ,SUBSTRING (CONVERT(VARCHAR(10),last_run_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),last_run_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),last_run_date),1,4) + '  ' +
        SUBSTRING (CONVERT(VARCHAR(6),last_run_time),1,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),last_run_time),3,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),last_run_time),5,2) as LRD
       ,SUBSTRING (CONVERT(VARCHAR(10),next_run_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),next_run_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),next_run_date),1,4) + '  ' +
        SUBSTRING (CONVERT(VARCHAR(6),next_run_time),1,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),next_run_time),3,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),next_run_time),5,2) as NRD
       ,dbo.label ((substring(xml,(patindex('%MediaPoolId%',Xml))+13,36)), (substring(xml,(patindex('%generation%',Xml))+12,4)), (substring(xml,(patindex('%vault%',Xml))+7,8))) as TL
       ,case 
			when substring(xml,(patindex('%vault%',Xml))+7,3) = 'off'  then 'Long-Term' 
			else 'Short-term'
       end as STLT
       ,case
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Fath' then 'Recovery Goal 1'
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Gran' then 'Recovery Goal 2'
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Grea' then 'Recovery Goal 3'
	end as RG
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 (def.Type = '913afd2d-ed74-47bd-b7ea-d42055e5c2f1' or def.Type = 'B5A3D25C-8EB2-4032-9428-C852DA5CE2C5')
and sch.IsDeleted = '0' and def.ProtectedGroupId is not null
order by FriendlyName, next_run_date, next_run_time
go
 
drop function label
go"

$result = Invoke-Sqlcmd -ServerInstance $instance -Query $query
$count = 1
write-host " The list below shows all scheduled backup to tape jobs (short term and long term)" -f green
write-host
if ($verbose.ToLower() -eq '')

{
	write-host " For optimun output, set PoweShell Width for screen buffer size to at least 200" -f yellow; write-host
	write-host
	write-host "     Protection Group name          Creation Date [Schedule Creation Date] [Last Run Date / time]   [Next Sched Run Date/time]  Goal type  Recovery Goal # Custom Tape Label"
	write-host "     ------------------------------ ------------- ------------------------ ----------------------   --------------------------  ---------- --------------- -----------------" 
	foreach ($result1 in $result)
	{
		if ($color -eq 'white') {$color = 'cyan'} else {$color = 'white'}
		write-host ("{0,2}"-f $count) -foreground green -nonewline
		write-host ( " - {0,-30} {1,-13} {2,-24} {3,-24} {4,-27} {5,-10} {6,15} " -f $result1.PG, $result1.SD, $result1.SCD, $result1.LRD, $result1.NRD, $result1.STLT, $result1.RG) -nonewline -f $color
		write-host $result1.TL -f yellow
		$count++
	}
}
else
{
	write-host " For optimun output, set PoweShell Width for screen buffer size  to at least 110" -f yellow; write-host
	write-host "     Protection Group               Term       Goal            Tape Label"
	write-host "     ------------------------------ ---------- --------------- --------------" 
	foreach ($result1 in $result)
	{
		if ($color -eq 'white') {$color = 'cyan'} else {$color = 'white'}
		write-host ("{0,2}"-f $count) -foreground green -nonewline
		write-host ( " - {0,-30} {1,-10} {2,15} " -f $result1.PG, $result1.STLT, $result1.RG) -nonewline -f $color
		write-host $result1.TL -f yellow
		$count++
	}
}		
May 30th, 2012 7:20am

Hi,

What order do you have your recovery goals set for.  They need to be in ascending order.

Goal-1 Daily
Goal-2 Monthly
Goal-3 Yearly

Else they might not run as expected.   Also, be aware that if you change goals around, that will effect current tape backups expiry dates.   We have a fix for that in the next DPM rollup to be released in July.

Right now, I don't see any yearly backups scheduled for January - so you will need to refresh your schedules by using the MODIFY button, then wait 30 minutes and re-run the script to see if new jobs were scheduled.

Free Windows Admin Tool Kit Click here and download it now
May 30th, 2012 3:55pm

Thanks Mike

I have tried your suggestion and will check to see if this has worked at the beggining of July. Fingers crossed.

June 25th, 2012 12:05pm

I have yet a question related to this.

Just set up a DPM 2012 SP1, it is protecting a single fileserver containing several TBs of data. Yesterday it finished running the initial replication job and created the first recovery points. Due to the amount of data and number of files, the DPM server is dedicated to protect this single file server.

We have the following SLA to meet:

Short term: 1 daily, retention 35 days

Long term: 1 monthly backup, retention 12 months

                  1 yearly backup, retention 5 years.

I want to schedule these long-term goals to run immidiately as this is necessary to meet my SLA's. However scheduling my long term goals, DPM decides that these will not run this month but for the monthly backup, next month and for the Yearly backup, next year.

My question is, how can I force DPM to run these long term jobs immidiately, as keep these jobs within the retention lifecycle?

I've attached the output from the script above, showing that my long term jobs are set to run the monthly backup last tuesday in february 2013 and for the yearly backup, last tuesday in January 2014.

Please advice

Cheers,

Claus

 

Free Windows Admin Tool Kit Click here and download it now
January 29th, 2013 9:54am

Clja,

You can make the backuptotape.ps1 powershell script from here to accomplish that.

http://social.technet.microsoft.com/Forums/en-US/dpmtapebackuprecovery/thread/4d9e8f47-33b6-4f57-82fa-6d25f6c43d80

January 29th, 2013 6:49pm

Thx Mike,

Looks promising, I'll try it out.

Also I stumpled across another post which you replied to earlier and in regard to DPM 2007/2010 and which might still be the case with DPM 2012.

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

Which in summary states that the long term schedule is configured within 24 hours from current time and date, DPM will schedule these from the next cycle (e.g. next month, next year). This is exactly explains what I am experiencing. 



Free Windows Admin Tool Kit Click here and download it now
January 29th, 2013 10:41pm

Hi,

Yes, that is the expected behavior, the script above will allow you to kick off the tape backup goal manually to compensate.

January 29th, 2013 11:25pm

Mike,

You mention above that this will be fixed by a future DPM patch. Is that available yet? If not, apart from frequently checking the schedule history, is there a way to proactively identify errors in the job schedule?

Thanks,

Free Windows Admin Tool Kit Click here and download it now
June 17th, 2013 4:39pm

Hi,

There are two issues here.

Issue1 - Expiry times on tapes change if you change recovery goals

<snip>
Also, be aware that if you change goals around, that will effect current tape backups expiry dates.   We have a fix for that in the next DPM rollup to be released in July.
>snip<

That is fixed in the following updates:

Description of hotfix rollup package 6 for System Center Data Protection Manager 2010    
http://support.microsoft.com/kb/2718797

Description of Update Rollup 2 for System Center 2012
http://support.microsoft.com/kb/2706783

Expiry dates for valid datasets that are already written to tape are changed when the retention range is changed during a protection group modification.

A protection group is configured for long-term tape recovery points together with custom long-term recovery goals. Recovery Goal 1 has a smaller retention range than the other recovery goals. In this configuration, if the protection group is changed to remove Recovery Goal 1 and to keep other recovery goals, datasets that were created by Recovery Goal 1 have their retention range changed to the retention range of the other recovery goals.

To avoid this problem, create the IsDatasetExpiryDateChangeInModifyPgAllowed DWORD value under the following registry key, and then set its value data to 0:

Issue 2 - Tape backups start on the wrong date.

BLOG-support-tip-scheduled-backup-to-tape-runs-on-a-wrong-date-on-dpm-2007-2010-and-2012
http://blogs.technet.com/b/dpm/archive/2012/09/24/support-tip-scheduled-backup-to-tape-runs-on-a-wrong-date-on-dpm-2007-2010-and-2012.aspx

June 17th, 2013 7:12pm

Hi,

This is a known issue that can occur in all current versions of DPM including DPM 2012, to correct the condition, you simply need to perform this step. Any time before the next scheduled (monthly or longer) backup, manually update the long term backup schedule by hitting the MODIFY button.  The code that runs when modifying the backup schedule redoes the bacup schedules and it fixes the scheduling issue. 

 

Below is a DPM Power-shell script that will show you the scheduled backups for all recovery goals for all protection groups.  You can see the last time each recovery goal ran, and the NEXT run time, so you can monitor the goals and re-fix them if necessary.

NOTE: It takes about 20-30 minutes before the fixed next run time schedule will be populated after modify the protection group and updating the schedule.

################################################################################################
#                                                                                              #
# This script will list all currently scheduled backup to tape jobs                            #
# It will list scheduled, last run and next run dates                                          #
#                                                                                              #
# Note: The script takes in consideration that the DPM Database was installed locally on its   #
#       Default instance. If SQL is installed on a different location/instance, edit the line  #
#       that starts with $instance = '.\msdpm2010                                              #
#                                                                                              #
# Author        : Wilson Souza                                                                 #
# Date Created  : 1/13/2012                                                                    #
# Last modified : 1/17/2012                                                                    #
# Version       : 1.0                                                                          #
#                                                                                              #
#                                                                                              #
# This version of the script was only tested on DPM 2010                                       #
#                                                                                              #
################################################################################################

param([string] $verbose)
add-pssnapin sqlservercmdletsnapin100
Add-PSSnapin -Name Microsoft.DataProtectionManager.PowerShell
$ConfirmPreference = 'None'
cls
$instance = '.\msdpm2010'  # <---- If DPM Database is on a different location, edit this line accordinly
$query = "use DPMDB
go
CREATE FUNCTION label (@GUID varchar(36), @kindred varchar(4), @vault varchar(8))
returns varchar (1024)
as
Begin
   declare @result varchar (1024)
   select @result = vaUltlabel from tbl_mm_vaultlabel where mediapoolid = @GUID and generation = 
      case  @kindred
          when 'Fath' Then '2'
          when 'Gran' then '1'
          when 'grea' Then '0'
      end and
      vault =
      case @vault
		  when 'Offsite1' then '3'
		  when 'Offsite2' then '4'
		  when 'Offsite3' then '5'
		  when 'Offsite4' then '6'
		  when 'Offsite5' then '7'
		  when 'Offsite6' then '8'
		  when 'Offsite7' then '9'
	  else
	      '1'
	  end
   RETURN @result
END
go
 
use DPMDB
select ScheduleId as name
       ,def.JobDefinitionId as JD
       ,FriendlyName as PG
       ,SUBSTRING (CONVERT(VARCHAR(10),active_start_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),active_start_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),active_start_date),1,4) as SD
       ,jobs.date_created as SCD
       ,SUBSTRING (CONVERT(VARCHAR(10),last_run_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),last_run_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),last_run_date),1,4) + '  ' +
        SUBSTRING (CONVERT(VARCHAR(6),last_run_time),1,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),last_run_time),3,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),last_run_time),5,2) as LRD
       ,SUBSTRING (CONVERT(VARCHAR(10),next_run_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),next_run_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),next_run_date),1,4) + '  ' +
        SUBSTRING (CONVERT(VARCHAR(6),next_run_time),1,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),next_run_time),3,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),next_run_time),5,2) as NRD
       ,dbo.label ((substring(xml,(patindex('%MediaPoolId%',Xml))+13,36)), (substring(xml,(patindex('%generation%',Xml))+12,4)), (substring(xml,(patindex('%vault%',Xml))+7,8))) as TL
       ,case 
			when substring(xml,(patindex('%vault%',Xml))+7,3) = 'off'  then 'Long-Term' 
			else 'Short-term'
       end as STLT
       ,case
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Fath' then 'Recovery Goal 1'
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Gran' then 'Recovery Goal 2'
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Grea' then 'Recovery Goal 3'
	end as RG
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 (def.Type = '913afd2d-ed74-47bd-b7ea-d42055e5c2f1' or def.Type = 'B5A3D25C-8EB2-4032-9428-C852DA5CE2C5')
and sch.IsDeleted = '0' and def.ProtectedGroupId is not null
order by FriendlyName, next_run_date, next_run_time
go
 
drop function label
go"

$result = Invoke-Sqlcmd -ServerInstance $instance -Query $query
$count = 1
write-host " The list below shows all scheduled backup to tape jobs (short term and long term)" -f green
write-host
if ($verbose.ToLower() -eq '')

{
	write-host " For optimun output, set PoweShell Width for screen buffer size to at least 200" -f yellow; write-host
	write-host
	write-host "     Protection Group name          Creation Date [Schedule Creation Date] [Last Run Date / time]   [Next Sched Run Date/time]  Goal type  Recovery Goal # Custom Tape Label"
	write-host "     ------------------------------ ------------- ------------------------ ----------------------   --------------------------  ---------- --------------- -----------------" 
	foreach ($result1 in $result)
	{
		if ($color -eq 'white') {$color = 'cyan'} else {$color = 'white'}
		write-host ("{0,2}"-f $count) -foreground green -nonewline
		write-host ( " - {0,-30} {1,-13} {2,-24} {3,-24} {4,-27} {5,-10} {6,15} " -f $result1.PG, $result1.SD, $result1.SCD, $result1.LRD, $result1.NRD, $result1.STLT, $result1.RG) -nonewline -f $color
		write-host $result1.TL -f yellow
		$count++
	}
}
else
{
	write-host " For optimun output, set PoweShell Width for screen buffer size  to at least 110" -f yellow; write-host
	write-host "     Protection Group               Term       Goal            Tape Label"
	write-host "     ------------------------------ ---------- --------------- --------------" 
	foreach ($result1 in $result)
	{
		if ($color -eq 'white') {$color = 'cyan'} else {$color = 'white'}
		write-host ("{0,2}"-f $count) -foreground green -nonewline
		write-host ( " - {0,-30} {1,-10} {2,15} " -f $result1.PG, $result1.STLT, $result1.RG) -nonewline -f $color
		write-host $result1.TL -f yellow
		$count++
	}
}		
Free Windows Admin Tool Kit Click here and download it now
January 27th, 2014 9:31pm

Hi,

Please use updated script (V1.6) from previous post - I tested it on DPM 2012 R2 using remote SQL Server and it works without modifications.

January 27th, 2014 10:53pm

Hi,

This is a known issue that can occur in all current versions of DPM including DPM 2012, to correct the condition, you simply need to perform this step. Any time before the next scheduled (monthly or longer) backup, manually update the long term backup schedule by hitting the MODIFY button.  The code that runs when modifying the backup schedule redoes the bacup schedules and it fixes the scheduling issue. 

 

Below is a DPM Power-shell script that will show you the scheduled backups for all recovery goals for all protection groups.  You can see the last time each recovery goal ran, and the NEXT run time, so you can monitor the goals and re-fix them if necessary.

NOTE: It takes about 20-30 minutes before the fixed next run time schedule will be populated after modify the protection group and updating the schedule.

################################################################################################
#                                                                                              #
# This script will list all currently scheduled backup to tape jobs                            #
# It will list scheduled, last run and next run dates                                          #
#                                                                                              #
# Note: The script takes in consideration that the DPM Database was installed locally on its   #
#       Default instance. If SQL is installed on a different location/instance, edit the line  #
#       that starts with $instance = '.\msdpm2010                                              #
#                                                                                              #
# Author        : Wilson Souza                                                                 #
# Date Created  : 1/13/2012                                                                    #
# Last modified : 1/17/2012                                                                    #
# Version       : 1.0                                                                          #
#                                                                                              #
#                                                                                              #
# This version of the script was only tested on DPM 2010                                       #
#                                                                                              #
################################################################################################

param([string] $verbose)
add-pssnapin sqlservercmdletsnapin100
Add-PSSnapin -Name Microsoft.DataProtectionManager.PowerShell
$ConfirmPreference = 'None'
cls
$instance = '.\msdpm2010'  # <---- If DPM Database is on a different location, edit this line accordinly
$query = "use DPMDB
go
CREATE FUNCTION label (@GUID varchar(36), @kindred varchar(4), @vault varchar(8))
returns varchar (1024)
as
Begin
   declare @result varchar (1024)
   select @result = vaUltlabel from tbl_mm_vaultlabel where mediapoolid = @GUID and generation = 
      case  @kindred
          when 'Fath' Then '2'
          when 'Gran' then '1'
          when 'grea' Then '0'
      end and
      vault =
      case @vault
		  when 'Offsite1' then '3'
		  when 'Offsite2' then '4'
		  when 'Offsite3' then '5'
		  when 'Offsite4' then '6'
		  when 'Offsite5' then '7'
		  when 'Offsite6' then '8'
		  when 'Offsite7' then '9'
	  else
	      '1'
	  end
   RETURN @result
END
go
 
use DPMDB
select ScheduleId as name
       ,def.JobDefinitionId as JD
       ,FriendlyName as PG
       ,SUBSTRING (CONVERT(VARCHAR(10),active_start_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),active_start_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),active_start_date),1,4) as SD
       ,jobs.date_created as SCD
       ,SUBSTRING (CONVERT(VARCHAR(10),last_run_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),last_run_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),last_run_date),1,4) + '  ' +
        SUBSTRING (CONVERT(VARCHAR(6),last_run_time),1,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),last_run_time),3,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),last_run_time),5,2) as LRD
       ,SUBSTRING (CONVERT(VARCHAR(10),next_run_date),5,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),next_run_date),7,2) + '-' + SUBSTRING (CONVERT(VARCHAR(10),next_run_date),1,4) + '  ' +
        SUBSTRING (CONVERT(VARCHAR(6),next_run_time),1,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),next_run_time),3,2) + ':' + SUBSTRING (CONVERT(VARCHAR(6),next_run_time),5,2) as NRD
       ,dbo.label ((substring(xml,(patindex('%MediaPoolId%',Xml))+13,36)), (substring(xml,(patindex('%generation%',Xml))+12,4)), (substring(xml,(patindex('%vault%',Xml))+7,8))) as TL
       ,case 
			when substring(xml,(patindex('%vault%',Xml))+7,3) = 'off'  then 'Long-Term' 
			else 'Short-term'
       end as STLT
       ,case
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Fath' then 'Recovery Goal 1'
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Gran' then 'Recovery Goal 2'
		when substring(xml,(patindex('%generation%',Xml))+12,4) = 'Grea' then 'Recovery Goal 3'
	end as RG
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 (def.Type = '913afd2d-ed74-47bd-b7ea-d42055e5c2f1' or def.Type = 'B5A3D25C-8EB2-4032-9428-C852DA5CE2C5')
and sch.IsDeleted = '0' and def.ProtectedGroupId is not null
order by FriendlyName, next_run_date, next_run_time
go
 
drop function label
go"

$result = Invoke-Sqlcmd -ServerInstance $instance -Query $query
$count = 1
write-host " The list below shows all scheduled backup to tape jobs (short term and long term)" -f green
write-host
if ($verbose.ToLower() -eq '')

{
	write-host " For optimun output, set PoweShell Width for screen buffer size to at least 200" -f yellow; write-host
	write-host
	write-host "     Protection Group name          Creation Date [Schedule Creation Date] [Last Run Date / time]   [Next Sched Run Date/time]  Goal type  Recovery Goal # Custom Tape Label"
	write-host "     ------------------------------ ------------- ------------------------ ----------------------   --------------------------  ---------- --------------- -----------------" 
	foreach ($result1 in $result)
	{
		if ($color -eq 'white') {$color = 'cyan'} else {$color = 'white'}
		write-host ("{0,2}"-f $count) -foreground green -nonewline
		write-host ( " - {0,-30} {1,-13} {2,-24} {3,-24} {4,-27} {5,-10} {6,15} " -f $result1.PG, $result1.SD, $result1.SCD, $result1.LRD, $result1.NRD, $result1.STLT, $result1.RG) -nonewline -f $color
		write-host $result1.TL -f yellow
		$count++
	}
}
else
{
	write-host " For optimun output, set PoweShell Width for screen buffer size  to at least 110" -f yellow; write-host
	write-host "     Protection Group               Term       Goal            Tape Label"
	write-host "     ------------------------------ ---------- --------------- --------------" 
	foreach ($result1 in $result)
	{
		if ($color -eq 'white') {$color = 'cyan'} else {$color = 'white'}
		write-host ("{0,2}"-f $count) -foreground green -nonewline
		write-host ( " - {0,-30} {1,-10} {2,15} " -f $result1.PG, $result1.STLT, $result1.RG) -nonewline -f $color
		write-host $result1.TL -f yellow
		$count++
	}
}		
Free Windows Admin Tool Kit Click here and download it now
January 28th, 2014 4:57pm

actually i got it to work using this in a batch file or shortcut... both jobs show up and it appears i can force one to run by selecting:

powershell.exe -nologo -file "c:\data\scripts\scheduledjobs.ps1"

(Right click, run as administrator on the shortcut)

January 28th, 2014 5:36pm

Mike/Wilson,

Thanks for the script. I really want it to run so I can run failed "copy to tape" jobs manually.

However, every time I run it, DPM crashes.

Event 7031:

The DPM service terminated unexpectedly....

Any tips?

PS: I am running DPM 2012 R2, UR6

Free Windows Admin Tool Kit Click here and download it now
August 14th, 2015 9:58am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics