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