Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup location Expand / Collapse
Author
Message
Posted Sunday, January 9, 2011 6:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 9, 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
Posted Sunday, January 9, 2011 6:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 6, 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
Posted Sunday, January 9, 2011 9:11 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:14 AM
Points: 1,618, Visits: 20,979
To add to the list, you can make use of the scripts mentioned here

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #1045105
Posted Monday, January 10, 2011 12:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 9, 2011 6:43 PM
Points: 7, Visits: 6
Thank you very much Dawne and Adiga!! I really appreciate your help!!
Post #1045133
Posted Tuesday, January 11, 2011 12:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 7:57 AM
Points: 2,173, Visits: 344
Thanks Good Scripts for the BACKUP LOCATIONS
Post #1045708
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse