Find Latest Backup

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8352

    Comments posted to this topic are about the item Find Latest Backup

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • Ivanna Noh

    SSCarpal Tunnel

    Points: 4138

    For some reason, when I ran this query in it's original form, it returned NULL rather than the name of the latest .bak file

    I changed the last line of the final select to:

    WHERE FileNames like ''+'%'+ @DatabaseName +'%'+''

    It seems to be working ok now

    a useful code snippet - thanks 🙂

  • jswong05

    Hall of Fame

    Points: 3503

    If someone took a backup into a folder only s/he knows, you are not going to find it this way. You should query msdb to find backup information.

    Regards,

    Jason

    http://usa.redirectme.net

    By the way, you may not have privilege to change sp_configure options. even with backup permission. Backup and admin are different things, permission can be granted separately.

    BOL: To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server role.

    Jason
    http://dbace.us
    😛

  • Oliver Wootton

    SSC Veteran

    Points: 291

    Why not use this script to get the latest backups ...

    SELECT DatabaseName=a.database_name, BackupDate=a.backup_date,

    PhysicalDeviceName=physical_device_name,

    BackupSize=backup_size,

    Duration=duration

    FROM

    (SELECT sd.name AS database_name,

    MAX(bs.backup_finish_date) AS backup_date

    FROM master.dbo.sysdatabases sd

    LEFT OUTER JOIN msdb.dbo.backupset bs ON sd.name = bs.database_name

    LEFT OUTER JOIN

    (

    SELECT sd.name AS database_name,

    MAX(bs.backup_finish_date) AS backup_date,

    bm.physical_device_name,

    bs.backup_size/1024/1024 as backup_size,

    DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) as duration

    FROM master.dbo.sysdatabases sd

    LEFT OUTER JOIN msdb.dbo.backupset bs ON sd.name = bs.database_name

    LEFT OUTER JOIN msdb.dbo.backupmediafamily bm ON bm.media_set_id = bs.media_set_id

    GROUP BY sd.name, bm.physical_device_name, bs.backup_size/1024/1024,

    DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date)

    ) sq1

    ON sq1.database_name = sd.name

    AND sq1.backup_date = bs.backup_finish_date

    GROUP BY sd.name ) a,

    (SELECT sd.name AS database_name,

    MAX(bs.backup_finish_date) AS backup_date,

    sq1.physical_device_name,

    sq1.backup_size,

    sq1.duration

    FROM master.dbo.sysdatabases sd

    LEFT OUTER JOIN msdb.dbo.backupset bs ON sd.name = bs.database_name

    LEFT OUTER JOIN --Must put in nested join

    (

    SELECT sd.name AS database_name,

    MAX(bs.backup_finish_date) AS backup_date,

    bm.physical_device_name,

    bs.backup_size/1024/1024 as backup_size,

    DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) as duration

    FROM master.dbo.sysdatabases sd

    LEFT OUTER JOIN msdb.dbo.backupset bs ON sd.name = bs.database_name

    LEFT OUTER JOIN msdb.dbo.backupmediafamily bm ON bm.media_set_id = bs.media_set_id

    GROUP BY sd.name, bm.physical_device_name, bs.backup_size/1024/1024,

    DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date)

    ) sq1

    ON sq1.database_name = sd.name

    AND sq1.backup_date = bs.backup_finish_date

    GROUP BY sd.name, bs.backup_finish_date, sq1.physical_device_name, sq1.backup_size, sq1.duration

    ) b

    where a.database_name=b.database_name

    and a.backup_date=b.backup_date

    ORDER BY DatabaseName

  • yogigollapudi

    Valued Member

    Points: 68

    i tried this script , but its not working ,

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply