Backup location

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Backup location

Backup location

Rate Topic

Display Mode

Topic Options

Author

Message

latokiro

Posted Sunday, January 09, 2011 6:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, January 09, 2011 6:43 PM
Points: 7, Visits: 6

Hi Friends,

How we can get the history of the database backed up.

Thank you very much.

Post #1045082

dwayne.bace

Posted Sunday, January 09, 2011 6:53 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, February 06, 2011 2:36 PM
Points: 16, Visits: 84

Here's some code that will give you what you want.

SELECT BS1.database_name,
(SELECT sum([size])/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
(
SELECT database_name as database_name,
MAX(backup_finish_date) as backup_finish_date
FROM msdb..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
WHERE BS1.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

Post #1045086