SQL Server Performance

Path of the backups in SQL server 2008, how do I see the path from the SQL server 2008 software

Discussion in 'ALL SQL SERVER QUESTIONS' started by Antonio Benildus Muerling, May 23, 2012.

  1. Hi all

    Thanks for reading my post, I am knowing where the backups are from, but am unable to see it from the software itself, would soneone be able to help me in, showing where would, or which window would have these showing the path in the SQL server 2008 softwar.

    I want to verify that these backups on this path have been backed up from this software,m before taking a step


  2. amahalingam New Member

    script below will show you the backups that were taken in the last 24hours and their path.

    SELECT database_name, backup_start_date,
    datediff(mi,backup_start_date, backup_finish_date) as Duration,
    CASE type WHEN 'D' then 'Database' ---type of backup
    WHEN 'I' THEN 'Differential Database'
    WHEN 'L' THEN 'Log'
    WHEN 'F' THEN 'File/FileGroup'
    WHEN 'G' THEN 'Differential File'
    WHEN 'P' THEN 'Partial'
    WHEN 'Q' THEN 'Differential Partial'
    ENDAS Bakup_Type, backup_size/(1024*1024*1024) as "backup_size in GB", --backup size in GB
    bmf.Physical_device_name ---this is to give the path of the file
    FROM msdb.dbo.backupset bs
    inner join msdb.dbo.backupmediafamily bmf ON
    bs.media_set_id= bmf.media_set_id
    --check for last 24 hours
    backup_start_date BETWEEN DATEADD(hh, -24*2, GETDATE()) AND GETDATE()
    --check for the specific time interval
    -- backup_start_date between '20100405 00:00:00' and '20100405 23:00:00'
    AND Type = 'D'--D for Database backup
    --and database_name = '<databasename>'
    ORDER BY backup_set_id DESC
    let me know if your questions are answered.

Share This Page