Thursday, May 30, 2013

DPM 2012 - Backup Protection Group to Tape

Update 2014-03-18: It appears there's been quite a few more revisions by Wilson, now version 1.6, since I first posted his version 1.0. I've updated the list below to reflect that latest version.

We've had a few instances where a backup job (long term to tape) fails. When we try to resume the backups by selecting "Resume tape backups...", it causes every data source in the protection group to backup to individual tapes rather than consolidating the backups onto one-tape (as it normally does on the scheduled jobs.)

We found this powershell script posted by from Mike Jacquet that works great to overcome the multiple tape issue! http://social.technet.microsoft.com/Forums/en-US/dpmtapebackuprecovery/thread/4fafbcb0-ac2c-4867-8434-31f1f5e532e0/#7b40ef6e-d8bd-4a24-aecd-5f1605e80225

After modifying it to work with DPM 2012 by adjusting line 25 to DPM 2012's SQL instance, $instance = '.\msdpm2012', we were able to run it to re-trigger one of our failed weekly tape backup. Hopefully this post will be a bit easier to find - but credit still goes to Mike and Wilson, I'm only the messenger. We did notice that you need to be sure and inactivate any alerts that are still active and it takes a minute or two to see the jobs kick off.


  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
#                                                                                              
# 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
}



 

6 comments:

rahulk34 said...

Nice post.There are many thing that i read first time.i found it helpful and informative. it is excellent and very informative..Thank you for sharing.

Apple service center in Delhi

Unknown said...

Given the Microsoft uneccesary complexity found in DPM for such a simple task as "Trigger this tape backup Now!", Justin's script is a Life Saver.

Thanks and Congratulations for a Job Well Done!

Justin Bennett said...

It is really Wilson Souza who deserves the praise. Just had to use his script again yesterday.

Anonymous said...

Great work. Just used the script to trigger yearly backup, because i had problems with scheduled job "not doing anything".

It must have to do something with this (response from another forum):

"Due to the way SQL Scheduler behaves, creating a monthly or a yearly backup
via DPM to start for a time within 24 hours from scheduling time, leads to
backups getting moved out by a month and year respectively." - ???

Whatever... Thank you very much.

mikemos said...

Thanks! The Access Manager service stopped over the weekend and I had to re-run all the weekly tape jobs.

I also found 2 groups that had an extra monthly tape job. Great script and good work!!!

PeteMate said...

Excellent, thank you.