Identify backup frequency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Identify backup frequency

Author

Topic

andriancruz
Starting Member

38 Posts

Posted - 2010-06-27 : 20:11:14

Hi everyone,

I have many backup frequency scheduled in my database environment. I just want to know if there is a way to identify the backup frequency if it's daily, weekly and monthly scheduled backup. I dont want to open one by one from the Jobs.

Any comments/suggestion is highly appriciated.

Thank you & regards,

Andrian

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-28 : 03:53:39

Well, I don't have a script for this but you could check out the system tables sysjobschedules (http://msdn.microsoft.com/en-us/library/ms188924.aspx) and sysschedules (http://msdn.microsoft.com/en-us/library/ms178644.aspx) in the msdb-database. They will provide the information you need...and I don't think the schema has changed since 2000 so I'm pretty sure you can use the documentation provided.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-06-28 : 16:39:30

I'd give credit where credit is do but I don't remember where I got this from. I do know I found it 2 years ago via Google, that's about it, so to the original author "thanks" and "Sorry I couldn't give you credit". The script could give all your history but I limited to the date I took over the server and forward (WHERE a.backup_start_date >= '2008-12-01').

SELECT a.server_name as 'Server',
a.database_name as 'Database',
case a.type
when 'D' then 'Full'
when 'I' then 'Differential'
when 'L' then 'Log'
when 'F' then 'File'
end
as 'Backup type',
convert(varchar(25),a.backup_start_date,100) AS 'Start Date',
convert(varchar(25),a.backup_finish_date,100) AS 'Finish Date',
DATENAME(weekday, a.backup_finish_date) AS 'Day' ,
datediff(minute, a.backup_start_date, a.backup_finish_date) as 'Mins' ,
cast(cast(datediff(minute, a.backup_start_date, a.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours' ,
case
when datediff(minute, a.backup_start_date, a.backup_finish_date) > 0
then cast(ceiling(a.backup_size /1048576) / datediff(minute, a.backup_start_date, a.backup_finish_date) as decimal (8,1))
else 0
end as 'Meg/Min',
ceiling(a.backup_size /1048576) as 'Size Meg' , cast((a.backup_size /1073741824) as decimal (9,2)) as 'Gig', -- div by 1073741824 to get gig
a.user_name,a.backup_size as 'Raw Size'
FROM msdb.dbo.backupset a
join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name
WHERE a.backup_start_date > '2008-12-01'
group by a.server_name, a.database_name, a.type, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
order by a.backup_finish_date desc, a.server_name, a.database_name

Terry

-- A word to the wise ain't necessary - it's the stupid ones that need the advice. -- Bill Cosby

andriancruz
Starting Member

38 Posts

Posted - 2010-06-28 : 23:00:07

Thank you guys for the link and scripts.

Cheers

Andrian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources