April 1, 2008 at 4:44 pm
Id like to make a query that asks the server if the daily backup maintenance plan went through successfully. What tables should I query? Also how do I query it since I lack complex tsql knowledge.
April 2, 2008 at 9:08 am
I found this somewhere (here maybe?) a while ago. Modify it to suit your needs but it shows me exactly what I'm looking for. We use NetBackup for some databases and native SQL for others and it shows me which database was backed up, what kind of backup, when it ran, when it completed, how big it was and who ran it.
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-01-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_start_date desc, a.server_name, a.database_name
-- You can't be late until you show up.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply