none
How to create an archive tape backup RRS feed

  • Question

  • Is there a way to increase the retention period of a tape backup after the backup has occurred?  We need to create archive backups around 4 times a year, which we will keep forever. It would be good to be able to take out one of the daily/weekly or monthly ba

    If it can't be done this way, what is the method for creating archive backups?

    Thanks,

    Bruce.

    Friday, March 2, 2012 8:42 PM

Answers

  • Hi,

    Each protection group can have up to three recovery goals, make one for "Every 3 months" with a X year retention.

    Now save the following below DPM Powershell script as backuptotape.ps1.  Run it and choose the above recovery goal from the list and a tape backup for that recovery goal will be made immediately.

    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 '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 optimum 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
    			$executingjob++
    		}
    	}
    }
    write-host
    if ($executingjob -gt 0)
    {
    	write-host "You selected to run $executingjob job(s). You can monitor job(s) progress via DPM 2010 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.

    Monday, March 5, 2012 2:35 AM
    Moderator

All replies

  • Hi,

    Each protection group can have up to three recovery goals, make one for "Every 3 months" with a X year retention.

    Now save the following below DPM Powershell script as backuptotape.ps1.  Run it and choose the above recovery goal from the list and a tape backup for that recovery goal will be made immediately.

    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 '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 optimum 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
    			$executingjob++
    		}
    	}
    }
    write-host
    if ($executingjob -gt 0)
    {
    	write-host "You selected to run $executingjob job(s). You can monitor job(s) progress via DPM 2010 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.

    Monday, March 5, 2012 2:35 AM
    Moderator
  • Thank you. I will try that.

    But what if I don't want one of my recovery goals set to every 3 months with a 5 year retention?

    I.e. what if I want it to be every month with a 12 month retention?

    Would it not be better if DPM only removes a catalogue for a tape if that tape is overwritten?

    That way I could just take out any daily or monthly tape (then press the write protect tape) and use that as an archive?

    Having to use a script for something that should be a routine part of the backup procedures seems a bit unnecessary.

    Thanks,

    Bruce.

    Tuesday, March 6, 2012 5:47 PM
  • Hi,

    Yes, if you don't want to configure the PG for a true long term protection goal, you can change the tape catalog pruning setting to be greater than the recovery goals in the PG and that should maintain the tape catalog longer.  The downside is that the SQL DB will grow as more tape catalogs are maintained and not pruned.


    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, March 7, 2012 2:23 AM
    Moderator
  • The problem is as you said, is that it will affect all the tapes when you may only need it to affect just the archive tapes (perhaps 4 a year), so that just isn't an option.

    The ideal solution is:

    a) For catalogues to be erased only when the tape has been re-written to (so it will naturally work as you need it to)

    or)

    b) Right click on a tape and click "For archive"

    Why can't DPM do either of these?

    Are you absolutely sure that a) doesn't happen?

    The reason I ask is that I have a weekly tape which I took out of circulation around 8 weeks ago (i.e. the retention has long since expired) and DPM still retains the catalogue fo this tape.

    Thanks,

    Bruce.

    Wednesday, March 7, 2012 8:53 PM
  • Hi,

    DPM will have knowledge of the tape after it expires and will maintain the list of data sets on the tape until it's overwritten, or marked as free.  You can right-click an expired tape and view tape details to see the data sets and you can even copy the data set to a network share or volume. However, the tape catalog that gets pruned is the file and directory list for each of the data sets on the tape.  Once the catalog gets pruned, if you try to restore from a recovery point under the recovery tab, it will say that the tape needs to be re-cataloged and will build a new one. 

    However, recovery points get deleted based on retention period, so recovery points for expired tapes should not be visible under the recovery tab. It isn't until you free the tape, then unfree the tape that it will show imported, then you can re-catalog it and the recovery point will show up under the "external DPM tapes" tree.  From there the only recovery option you have is to restore to a network share or volume on a protected server.  If the recovery point was for application data (like SQL, Exchange, sharepoint) you cannot restore it as application data back to the original location, only as flat files to network share, or volume. So the recovery from expired tapes is very limited, and it's really best to setup up a long term recovery goal so it can be restored properly and easily from under the recovery tab.


    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, March 8, 2012 6:06 AM
    Moderator