January 9, 2011 at 6:04 pm
Hi Friends,
How we can get the history of the database backed up.
Thank you very much.
January 9, 2011 at 6:53 pm
Here's some code that will give you what you want.
SELECTBS1.database_name,
(SELECTsum()/128 from sys.master_files
where ((database_id = DB_ID(BS1.database_name)) AND (type_desc='ROWS'))) as database_size_MB,
ISNULL(DateDiff(minute, BS1.backup_start_date,getdate()) , 99999) as minutes_since_last_backup,
BS1.backup_start_date,
BS1.backup_finish_date,
cast(BS1.backup_size /1024/1024 as int) as backup_size_MB,
BF1.physical_device_name
FROM msdb..backupset BS1 join msdb..backupmediafamily BF1
on BS1.media_set_id = BF1.media_set_id join
(
SELECTdatabase_name as database_name,
MAX(backup_finish_date) as backup_finish_date
FROMmsdb..backupset BS join msdb..backupmediafamily BF
on BS.media_set_id = BF.media_set_id
WHERE BS.Type='D'
GROUP BY database_name, type
) LB
on LB.database_name = BS1.database_name and LB.backup_finish_date = BS1.backup_finish_date
WHEREBS1.database_name NOT IN ('tempdb')
AND DatabasePropertyEx(BS1.database_name,'Status') = 'ONLINE'
AND DatabasePropertyEx(BS1.database_name,'Updateability') = 'READ_WRITE'
ORDER BY BS1.database_name
January 9, 2011 at 9:11 pm
To add to the list, you can make use of the scripts mentioned here
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
January 10, 2011 at 12:27 am
Thank you very much Dawne and Adiga!! I really appreciate your help!!
January 11, 2011 at 12:04 am
Thanks Good Scripts for the BACKUP LOCATIONS
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply