none
Monthly and Yearly backups not working for Protection Group? RRS feed

  • Question

  • 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?

    Monday, May 21, 2012 2:33 PM

Answers

  • Hi,

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


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    • Proposed as answer by Claus Jacobsen Wednesday, January 30, 2013 7:30 PM
    • Marked as answer by dewsburymike Thursday, February 21, 2013 3:24 PM
    Tuesday, January 29, 2013 11:25 PM
    Moderator

All replies

  • 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
    }
    
    



    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.


    Wednesday, May 30, 2012 3:11 AM
    Moderator
  • 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++
    	}
    }


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    What name we should give this code ps1 ? I named as "ScheduleHistoryRecovery.ps1"

    However I don't see the sequence number of tape label on "Custom Tape Label"



    • Edited by Oneoa Wednesday, May 30, 2012 7:35 AM
    Wednesday, May 30, 2012 7:20 AM
  • 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.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, May 30, 2012 3:55 PM
    Moderator
  • Thanks Mike

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

    Monday, June 25, 2012 12:05 PM
  • 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

     

    Tuesday, January 29, 2013 9:54 AM
  • 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


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, January 29, 2013 6:49 PM
    Moderator
  • 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. 



    Tuesday, January 29, 2013 10:41 PM
  • Hi,

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


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    • Proposed as answer by Claus Jacobsen Wednesday, January 30, 2013 7:30 PM
    • Marked as answer by dewsburymike Thursday, February 21, 2013 3:24 PM
    Tuesday, January 29, 2013 11:25 PM
    Moderator
  • 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,

    Monday, June 17, 2013 4:39 PM
  • 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


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, June 17, 2013 7:12 PM
    Moderator
  • 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++
    	}
    }


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    I cant seem to get this script to run.. can someone point out what might be missing..

    I've tried creating a shortcut like the following as well:

    Target: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -noexit -File "C:\Program Files\Microsoft DPM\DPM\bin\dpmcliinitscript.ps1" "C:\Data\scripts\scheduledjobs.ps1"

    Start in: "C:\Program Files\Microsoft DPM\DPM\bin\"

    If i run as admin in just takes you to a command line prompt in PS..

    Any thoughts on what is missing here.. i've modified the ps1 to reflect 2012 in my case.. 2012 R2 DPM


    Tech, the Universe, Everything: http://tech-stew.com

    Monday, January 27, 2014 9:31 PM
  • 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.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, January 27, 2014 10:53 PM
    Moderator
  • 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++
    	}
    }


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    I cant seem to get this script to run.. can someone point out what might be missing..

    I've tried creating a shortcut like the following as well:

    Target: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -noexit -File "C:\Program Files\Microsoft DPM\DPM\bin\dpmcliinitscript.ps1" "C:\Data\scripts\scheduledjobs.ps1"

    Start in: "C:\Program Files\Microsoft DPM\DPM\bin\"

    If i run as admin in just takes you to a command line prompt in PS..

    Any thoughts on what is missing here.. i've modified the ps1 to reflect 2012 in my case.. 2012 R2 DPM


    Tech, the Universe, Everything: http://tech-stew.com

    Ok, the 1.6 script works.. but only if i run it from the ps1 script file.. by right clicking it and doing the Run with Powershell option (non admin).. my shortcut doesnt work.. same issue.. it just goes to a power shell prompt.  The right click and run is non admin mode as well.

    When I run it with the right click it shows no jobs scheduled.. If i look in dpm 2012 r2 i see a yearly tape backup scheduled for today.. the script doesnt show me a way to also get the monthly to appear (i've set the monthly for a day later but it doesnt appear in the scheduled tasks for next 7 days).. i was under the impression this script would help force the monthly to appear/run.. but even the yearly that does show in DPM scheduled tasks isnt showing up with the script (maybe admin issue?)


    Tech, the Universe, Everything: http://tech-stew.com

    Tuesday, January 28, 2014 4:57 PM
  • 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)


    Tech, the Universe, Everything: http://tech-stew.com


    • Edited by techfun89 Tuesday, January 28, 2014 5:37 PM
    Tuesday, January 28, 2014 5:36 PM
  • 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


    -Rajeev rajdude.com

    Friday, August 14, 2015 1:57 PM