Backup location

  • Hi Friends,

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

    Thank you very much.

  • 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

  • To add to the list, you can make use of the scripts mentioned here

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • Thank you very much Dawne and Adiga!! I really appreciate your help!!

  • 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