none
how to check next schedule of backup ? RRS feed

  • Question

  • On My Protection Group i Set to backup to tape every 5 weeks

    because i want to insert tape correct with backup time .

    how to check next time to backup schedule of my protection group ?

    best regards


    chatchai-netd

    Thursday, February 5, 2015 4:01 AM

All replies

  • Hi,

    You can use the below DPM powershell script to display future tape backup scheduled times.  If you modify a protection group - it may take up to 20 minutes before the scheduled time is updated.

    ################################################################################################
    #                                                                                              #
    # 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 = '.\msdpm2012                                              #
    #                                                                                              #
    # 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                                       #
    # But seems to work on DPM 2012 with proper Instance name                                      #
    ################################################################################################
    
    param([string] $verbose)
    add-pssnapin sqlservercmdletsnapin100
    Add-PSSnapin -Name Microsoft.DataProtectionManager.PowerShell
    $ConfirmPreference = 'None'
    cls
    $instance = '.\msdpm2012'  # <---- 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 optimum 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.

    Thursday, February 5, 2015 10:33 PM
    Moderator