none
Never expire Option in Tapes RRS feed

  • Question

  • Hi Guys,

    Is there any way I could set up a job normal job where I could set the tape not to expire ? I have checked co-location but it only gives me protection period. Please help.

    Thanks,


    Friday, October 28, 2016 2:55 PM

All replies

  • Hi, the best you can do is add a Yearly recovery goal and set it to 99 years.  Then use the below powershell script to make a new yearly tape backup for that PG.

    #version 1.6
    
    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.

    Friday, October 28, 2016 3:59 PM
    Moderator