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