MSSQL – Backup Auditing Query

use msdb
go
DECLARE @dbname sysname
SET @dbname = NULL –set this to be whatever dbname you want
SELECT
bup.server_name AS [Server],
bup.database_name AS [Database],
CONVERT(DECIMAL(9,2),bup.compressed_backup_size/1024/1024/1024.0) compressed_backup_sizeInGB,
CONVERT(DECIMAL(9,2),backup_size/1024/1024/1024.0) backup_sizeInGB,
bup.backup_start_date AS [Backup Started],
bup.backup_finish_date AS [Backup Finished]
,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ‘ hours, ‘

CAST(((CAST(DATEDIFF(ss, bup.backup_start_date, bup.backup_finish_date) AS int))/60)%60 AS varchar)+ ‘ minutes, ‘
CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ‘ seconds’
AS [Total Time],
case when bup.backup_finish_date is null then ‘Not Completed’ Else ‘Çompleted’ END Status
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
(SELECT MAX(backup_set_id) FROM msdb.dbo.backupset
WHERE database_name = ISNULL(@dbname, database_name) –if no dbname, then return all
AND type = ‘D’ –only interested in the time of last full backup
GROUP BY database_name)
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name

SELECT js.step_name as TSMPush, case run_status when 1 then ‘Completed’ else ‘Failed’ end as Status,
–h.step_id, h.job_id, run_date, run_time, retries_attempted, [message],

    [start_date] = CONVERT(DATETIME, RTRIM(run_date) + ' '
    + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),
    ' ','0'),3,0,':'),6,0,':')),
        durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
    ' ','0'),4,0,':'),7,0,':')

FROM msdb.dbo.[sysjobhistory] h
INNER JOIN msdb.dbo.sysjobs AS j on j.job_id = h.job_id
iNNER JOIN msdb.dbo.sysjobsteps AS js on j.job_id = js.job_id and h.step_id =js.step_id
where j.name = ‘DBAG Backup Database – zzzTOTALzzz’
and h.step_id =2
order by run_date

Declare @daysToAverage smallint = 30;

Declare @avgRunTime Table
(
job_id uniqueidentifier
, avgRunTime int
);

/* We need to parse the schedule into something we can understand */
Declare @weekDay Table (
mask int
, maskValue varchar(32)
);

Insert Into @weekDay
Select 1, ‘Sunday’ Union All
Select 2, ‘Monday’ Union All
Select 4, ‘Tuesday’ Union All
Select 8, ‘Wednesday’ Union All
Select 16, ‘Thursday’ Union All
Select 32, ‘Friday’ Union All
Select 64, ‘Saturday’;

/* First, let’s get our run-time average */
Insert Into @avgRunTime
Select job_id
, Avg((run_duration/10000) * 3600 + (run_duration/100%100)60 + run_duration%100) As ‘avgRunTime’ / convert HHMMSS to seconds */
From msdb.dbo.sysjobhistory
Where step_id = 0 — only grab our total run-time
And run_status = 1 — only grab successful executions
And msdb.dbo.agent_datetime(run_date, run_time) >= DateAdd(day, -@daysToAverage, GetDate())
Group By job_id;

/* Now let’s get our schedule information */
With myCTE
As(
Select sched.name As ‘scheduleName’
, sched.schedule_id
, jobsched.job_id
, sched.enabled
, Case When sched.freq_type = 1 Then ‘Once’
When sched.freq_type = 4
And sched.freq_interval = 1
Then ‘Daily’
When sched.freq_type = 4
Then ‘Every ‘ + Cast(sched.freq_interval As varchar(5)) + ‘ days’
When sched.freq_type = 8 Then
Replace( Replace( Replace((
Select maskValue
From @weekDay As x
Where sched.freq_interval & x.mask <> 0
Order By mask For XML Raw)
, ‘”/>’, ”)
+ Case When sched.freq_recurrence_factor <> 0
And sched.freq_recurrence_factor = 1
Then ‘; weekly’
When sched.freq_recurrence_factor <> 0 Then ‘; every ‘
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ‘ weeks’ End
When sched.freq_type = 16 Then ‘On day ‘
+ Cast(sched.freq_interval As varchar(10)) + ‘ of every ‘
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ‘ months’
When sched.freq_type = 32 Then
Case When sched.freq_relative_interval = 1 Then ‘First’
When sched.freq_relative_interval = 2 Then ‘Second’
When sched.freq_relative_interval = 4 Then ‘Third’
When sched.freq_relative_interval = 8 Then ‘Fourth’
When sched.freq_relative_interval = 16 Then ‘Last’
End +
Case When sched.freq_interval = 1 Then ‘ Sunday’
When sched.freq_interval = 2 Then ‘ Monday’
When sched.freq_interval = 3 Then ‘ Tuesday’
When sched.freq_interval = 4 Then ‘ Wednesday’
When sched.freq_interval = 5 Then ‘ Thursday’
When sched.freq_interval = 6 Then ‘ Friday’
When sched.freq_interval = 7 Then ‘ Saturday’
When sched.freq_interval = 8 Then ‘ Day’
When sched.freq_interval = 9 Then ‘ Weekday’
When sched.freq_interval = 10 Then ‘ Weekend’
End
+ Case When sched.freq_recurrence_factor <> 0
And sched.freq_recurrence_factor = 1 Then ‘; monthly’
When sched.freq_recurrence_factor <> 0 Then ‘; every ‘
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ‘ months’ End
When sched.freq_type = 64 Then ‘StartUp’
When sched.freq_type = 128 Then ‘Idle’
End As ‘frequency’
, IsNull(‘Every ‘ + Cast(sched.freq_subday_interval As varchar(10)) +
Case When sched.freq_subday_type = 2 Then ‘ seconds’
When sched.freq_subday_type = 4 Then ‘ minutes’
When sched.freq_subday_type = 8 Then ‘ hours’
End, ‘Once’) As ‘subFrequency’
, Replicate(‘0’, 6 – Len(sched.active_start_time))
+ Cast(sched.active_start_time As varchar(6)) As ‘startTime’
, Replicate(‘0’, 6 – Len(sched.active_end_time))
+ Cast(sched.active_end_time As varchar(6)) As ‘endTime’
, Replicate(‘0’, 6 – Len(jobsched.next_run_time))
+ Cast(jobsched.next_run_time As varchar(6)) As ‘nextRunTime’
, Cast(jobsched.next_run_date As char(8)) As ‘nextRunDate’
From msdb.dbo.sysschedules As sched
Join msdb.dbo.sysjobschedules As jobsched
On sched.schedule_id = jobsched.schedule_id
Where sched.enabled = 1
)

/* Finally, let’s look at our actual jobs and tie it all together / Select job.name As ‘jobName’ , sched.scheduleName , sched.frequency , sched.subFrequency ,SUSER_SNAME(job.owner_sid) as Owner , CASE job.enabled WHEN 1 THEN ‘Yes’ else ‘No’ END as Enabled , CASE sched.enabled WHEN 1 THEN ‘Yes’ else ‘No’ END as Scheduled , c.name AS Category , SubString(sched.startTime, 1, 2) + ‘:’ + SubString(sched.startTime, 3, 2) + ‘ – ‘ + SubString(sched.endTime, 1, 2) + ‘:’ + SubString(sched.endTime, 3, 2) As ‘scheduleTime’ — HH:MM , SubString(sched.nextRunDate, 1, 4) + ‘/’ + SubString(sched.nextRunDate, 5, 2) + ‘/’ + SubString(sched.nextRunDate, 7, 2) + ‘ ‘ + SubString(sched.nextRunTime, 1, 2) + ‘:’ + SubString(sched.nextRunTime, 3, 2) As ‘nextRunDate’ / Note: the sysjobschedules table refreshes every 20 min,
so nextRunDate may be out of date */
, art.avgRunTime As ‘avgRunTime_inSec’ — in seconds
, (art.avgRunTime / 60) As ‘avgRunTime_inMin’ — convert to minutes

From msdb.dbo.sysjobs As job
JOIN msdb.dbo.syscategories c (NOLOCK) ON job.category_id = c.category_id
Join myCTE As sched
On job.job_id = sched.job_id
Left Join @avgRunTime As art
On job.job_id = art.job_id
Where job.enabled = 1 — do not display disabled jobs
Order By nextRunDate;
go

SET NOCOUNT ON
DECLARE @MaxLength INT
SET @MaxLength = 50

DECLARE @xp_results TABLE (
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)

DECLARE @job_owner sysname

DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember (‘sysadmin’), 0)
SET @job_owner = suser_sname ()

INSERT INTO @xp_results
EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

UPDATE @xp_results
SET last_run_time = right (‘000000’ + last_run_time, 6),
next_run_time = right (‘000000’ + next_run_time, 6)

SELECT j.name AS JobName,
j.enabled AS Enabled,
sl.name AS OwnerName,
CASE x.running
WHEN 1
THEN
‘Running’
ELSE
CASE h.run_status
WHEN 2 THEN ‘Inactive’
WHEN 4 THEN ‘Inactive’
ELSE ‘Completed’
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ ‘-‘
+ substring (x.last_run_date, 5, 2)
+ ‘-‘
+ substring (x.last_run_date, 7, 2)
+ ‘ ‘
+ substring (x.last_run_time, 1, 2)
+ ‘:’
+ substring (x.last_run_time, 3, 2)
+ ‘:’
+ substring (x.last_run_time, 5, 2)
+ ‘.000’,
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN ‘Fail’
WHEN 1 THEN ‘Success’
WHEN 2 THEN ‘Retry’
WHEN 3 THEN ‘Cancel’
WHEN 4 THEN ‘In progress’
END
AS LastRunOutcome,
durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
‘ ‘,’0′),4,0,’:’),7,0,’:’)

FROM @xp_results x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
LEFT OUTER JOIN sys.syslogins sl ON j.owner_sid = sl.sid
order by LastRunTime,Jobname asc

Midsund

IT Consultancy & WebSite Creation

MSSQL – Backup Auditing Query