February 3, 2009 at 5:57 am
I've inherited a server with shedloads of databases on it. The customer has asked me;
Could you list the database names and how often they are backed up for Athena and Altair. For example:
DatabaseBackup Frequency:
MyDatabaseDaily at 18:00
Is there anyway of doing this by querying the system tables? I really don't fancy trawling through hundreds of jobsteps and transcribing the details.
Thanks.
February 3, 2009 at 7:35 am
There's a backup history table somewhere in msdb. Backupset I think
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2009 at 7:36 am
I tried this on SQL 2005. It is a little crude, but should get you a good start on what you are looking for:
SELECT sysjobs.NAME, sysjobsteps.database_name, sysjobschedules.next_run_date AS "YYYYMMDDD", sysjobschedules.next_run_time AS "HHMMSS", sysjobs.date_created, sysjobsteps.step_id, sysjobsteps.step_name, sysjobsteps.command
FROM sysjobs
JOIN sysjobsteps ON sysjobs.job_id = sysjobsteps.job_id
JOIN sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
WHERE sysjobs.enabled = 1
I forgot to add previously that you need to run this on the MSDB Database. Sorry :w00t:
Regards, Irish
February 3, 2009 at 8:36 am
Thanks this looks exactly what I need as a strating point. If I add details from sysschedules, it should do the trick, I'll let you know how I get on.
February 3, 2009 at 8:41 am
fred.coleman (2/3/2009)
Thanks this looks exactly what I need as a strating point. If I add details from sysschedules, it should do the trick, I'll let you know how I get on.
Sysschedules only helps if the backups are initiated by a SQL Server job. If the backups are started through scheduled tasks or a third party backup is used you won't find those.
In msdb..backupsets you find all backups which have been made, no matter how it was initiated.
[font="Verdana"]Markus Bohse[/font]
February 3, 2009 at 8:45 am
Mark,
You make a good point. There is more in the MSDB that can assist in determining backups and all that. I also thought about pulling Maintenance Plan information into that Join, but did not think that it fit into the parameters of the question posted.
My thought was to provide a starting point and roll from there.
Regards, Irish
February 3, 2009 at 10:32 am
Thank you everyone. This does what I need;
use MSDB
go
SELECTsysjobs.NAME,
sysjobsteps.step_name,
sysjobsteps.command,
sysschedules.name,
CASE
WHEN freq_type = 1 THEN 'Once only'
WHEN freq_type = 4 THEN 'Daily'
WHEN freq_type = 8 THEN 'Weekly'
WHEN freq_type = 16 THEN 'Monthly'
WHEN freq_type = 32 THEN 'Monthly'
WHEN freq_type = 64 THEN 'SQL Agent Startup'
WHEN freq_type = 128 THEN 'Computer Idle'
ELSE 'Invalid Frequency'
END AS 'Frequency Type',
CASE freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN Cast(freq_interval as varchar(20))
WHEN 8 THEN
CASE Cast(freq_interval as varchar(20))
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 4 THEN 'Tuesday'
WHEN 8 THEN 'Wednesday'
WHEN 16 THEN 'Thursday'
WHEN 32 THEN 'Friday'
WHEN 64 THEN 'Saturday'
ELSE 'Multiple Days'
END
WHEN 16 THEN Cast(freq_interval as varchar(20))
WHEN 32 THEN
CASE Cast(freq_interval as varchar(20))
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'Day'
WHEN 9 THEN 'Weekday'
WHEN 10 THEN 'Weekend day'
ELSE 'Multiple Days'
END
END AS 'Frequency_Interval',
sysjobschedules.next_run_date AS "Next Run Date",
CASE
WHEN sysjobschedules.next_run_time < 100000 then '0' + CAST(sysjobschedules.next_run_time as char(6))
ELSE CAST(sysjobschedules.next_run_time as char(6))
END AS "Next Run Time"
FROM sysjobs
JOIN sysjobsteps ON sysjobs.job_id = sysjobsteps.job_id
JOIN sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
JOIN sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE sysjobs.enabled = 1
AND sysjobsteps.command LIKE '%BACKUP%'
AND sysjobsteps.step_name NOT LIKE '%failed%'
ORDER BY sysjobs.name
I take your point about 3rd party backups and scheduled tasks, fortunately they're not an issue in this case.
August 18, 2009 at 8:16 am
Thank you for posting your findings. I am working on an SSRS report (or sweet of reports/dashboard) that will sum up the day's agent jobs, providing pass/fail status as well as the ability to drill down into more detail of the job. Your query is a good start for me. I will post my work after it is working for others to scrutinize and provide feed back.
I am also working on a SSRS report or possibly a MS Outlook plug-in that will display the scheduled jobs in a calendar/meeting request way so that a DBA can “see” how jobs they have running at a given time in a given month.
A big thanks to all.
August 19, 2009 at 5:21 am
Hi guys,
backup history is stored in msdb in a set of tables. You can query them with this:
select A.database_name, A.backup_start_date, A.backup_finish_date,
datediff(ss, A.backup_start_date, A.backup_finish_date) as 'Duration',
(case A.[type] when 'D' then 'Full' when 'I' then 'Differential' when 'L' then 'Log'
when 'F' then 'File or Filegroup' when 'G' then 'File Differential'
when 'P' then 'Partial' when 'Q' then 'Partial Differential' else A.[type] end) as 'Type',
A.backup_size, B.physical_device_name
from msdb.dbo.backupset A
left join msdb.dbo.backupmediafamily B on B.media_set_id = A.media_set_id
order by A.backup_finish_date desc
August 23, 2009 at 4:05 pm
With supporting over 200 instances, we implemented an SSIS packages that connects to all instances each morning to determine the last time the DBs were backed up using the same tables mentioned earlier in the article.
Based on that data, we maintain a table of when the DBs are expected to be backed up (i.e 1 day, 30 days, etc), and build a report to do a datediff to find out which DBs were not backed up within the expected timeframe.
You could also use that data to do the same to find out when the backups take place. Only draw back is it would need to collect data daily for a month or two for the reports to be accurate.
Just offering another approach to get at the data.
Steve
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply