April 21, 2009 at 10:57 am
Hi,
I need information whether latest netbackup 6.5 does log backup history to msdb database regulary.
I need to retrieve the backup information and SQL backup is schedule using Netbackup.
April 21, 2009 at 5:07 pm
Hi Hemant,
I don't whether netbackup makes an entry or not once backup is done. You can try by taking backup, and run this query. If backup finishes successfully you should see a record with latest datetime when executed .
select s1.database_name,s1.backup_start_date,s2.physical_device_name
from msdb.dbo.backupset s1 inner join
msdb.dbo.backupmediafamily s2
on s1.media_set_id = s2.media_set_id
where s1.type ='typeofbackup'
and s1.database_name ='
order by s1.backup_start_date desc
typeof backup 'D' full backup ,'I' Differential Backup and 'L' log backup
April 22, 2009 at 7:39 am
It definitely does log all the backup information. This may be overkill but if you look at user_name, you can tell who ran the backup and decipher which is netbackup or native.
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
--Edit: Sorry :pinch:, just noticed 2005. I run this script in 2000 so it may not exactly work but the information is there.
-- You can't be late until you show up.
April 23, 2009 at 8:01 am
I got this error when I ran your script.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'D'.
Msg 105, Level 15, State 1, Line 9
Unclosed quotation mark after the character string ' log backup
April 23, 2009 at 8:38 am
Hmmm, runs fine on my server (again, SQL 2000). Maybe something lost in the copy paste, it's happened to me before. A single quote(') comes across as a different single quote format, more of an left-angled quote (it's not on my keyboard so I cannot represent it here). Try replacing each of the quotes.
BTW, I just copied/pasted and ran the script in QA and it ran fine.
-- Edit: I just ran it on our only SQL2005 box and it ran fine there too.
-- You can't be late until you show up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply