none
How To Manage Tapes for Weekly and Monthly LT Backups and a Single Drive RRS feed

  • Question

  • Hello

    I need some advice on how to manage media reporting when there are certain tapes which I don't want to ever show as expired (ready for reuse) in DPM.  Specifically, is there a way to modify the expiration time of a tape after it has been written by DPM?

    Some Background:

    We have a newly installed DPM 2010 system that we are testing as a replacement to our Backup Exec system.  We are using disk for short-term protection and one partition of a Dell TL2000 for long-term backups to tape.  Our LT backup objectives are to keep 4 weekly backups offsite along with monthly offsite backups (kept for 7 years).

    Since we have only a single tape drive in the library partition I don't have the option of having DPM make monthly copies of the weekly tapes via multiple LT objectives.  Our intended workaround is to allow DPM to make the weekly tapes and then just rotate out the last weekly tape set for each given month.  The only problem is that we don't want those "weekly" tapes to show up in the expired tapes report since we have no intention of brining them back onsite and reusing them.

    I've seen this thread about removing old tapes but I'm not sure of the downside to completely deleting tapes from the DB that I might one day wish to restore data from.

    Any advice would be greatly appreciated.

    Thanks

    Steve

    Tuesday, February 14, 2012 12:36 PM

Answers

  • Hi steve,

    See if this Powershell script works.  Go ahead and add your monthly tape backup recovery goal with the 7 year retention. Copy the below script and save it as backuptotape.ps1. Insert a tape to the tape drive and make sure it shows as free in the DPM library GUI.  Run the script and select to run the backup for the Monthly recovery goal and see if that works.  I don't know if it's going to try to do a tape copy, or actually make a recovery point from a disk based snapshot.

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

    Let me know what happens.

    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, February 15, 2012 10:34 PM
    Moderator

All replies

  • Hi steve,

    See if this Powershell script works.  Go ahead and add your monthly tape backup recovery goal with the 7 year retention. Copy the below script and save it as backuptotape.ps1. Insert a tape to the tape drive and make sure it shows as free in the DPM library GUI.  Run the script and select to run the backup for the Monthly recovery goal and see if that works.  I don't know if it's going to try to do a tape copy, or actually make a recovery point from a disk based snapshot.

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

    Let me know what happens.

    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, February 15, 2012 10:34 PM
    Moderator
  • Hi Mike

    Thanks for your reply.  We're due to run our month-end tapes next week so I'll give the script a try then and report back the results.

    Kind regards

    Steve

    Tuesday, February 21, 2012 9:13 AM
  • Hi Mike.

    I tried the script yesterday and it worked perfectly.  Thank you very much for your solution.

    Kind regards

    Steve

    Tuesday, February 28, 2012 9:10 AM
  • Mike, this script worked perfectly for me. Thank you SO much!
    Tuesday, September 2, 2014 6:18 AM
  • Hello,

    I get no Output of scheduled Jobs in DPM 2012R2

     The list below shows all scheduled backup to tape jobs (short term and long term)

     For optimun output, set PoweShell Width for screen buffer size  to at least 110

         Protection Group               Term       Goal            Tape Label
         ------------------------------ ---------- --------------- --------------

    Which job(s) you want to run? If running more than one job enter numbers separated by space:

    But I have many scheduled Jobs for my PG, daily and weekly, but I will manualy do a yearly backup an put it in my safe.

    Thursday, June 18, 2015 7:10 AM
  • Did you launch it as Administrator?
    Thursday, June 18, 2015 8:03 AM
  • S H I T

    Now it works! Thx

    Thursday, June 18, 2015 8:16 AM
  • Haha! Caught me out, too.

    PS: Feel free to vote me up...

    Thursday, June 18, 2015 9:46 AM