Database Backup Information Scripts

  • Sathya Praveen

    SSC Rookie

    Points: 38

    Comments posted to this topic are about the item Database Backup Information Scripts

  • SQL Guy 1

    SSCoach

    Points: 15726

    Good scripts.

    Couple of notes:

    1. Besides D and L types of backup, there is also P = partial.

    2. It would be also helpful to include database_name in select part of "Most Recent Database Backup for Each Database - Detailed"

  • Lee Linares

    SSCrazy

    Points: 2662

    Sathya,

    Thanks for sharing the script with us. To add to SQL Guy 1's comment, you should also include I (Differential) to backup types. Thanks again.

    Lee

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Nice script, thanks.

  • SQL Galaxy

    One Orange Chip

    Points: 29228

    Hi Satya.. this is excellent script for sharing..

    How can I add linked server name in this script for getting backup report in one single format.

    I have created linked server, Name is : MNDLIVPROD

    SELECT

    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

    msdb.dbo.backupset.database_name,

    msdb.dbo.backupset.backup_start_date,

    msdb.dbo.backupset.backup_finish_date,

    --msdb.dbo.backupset.expiration_date,

    CASE msdb..backupset.type

    WHEN 'D' THEN 'Database'

    WHEN 'L' THEN 'Log'

    END AS backup_type,

    msdb.dbo.backupset.backup_size,

    msdb.dbo.backupmediafamily.logical_device_name,

    msdb.dbo.backupmediafamily.physical_device_name

    --msdb.dbo.backupset.name AS backupset_name,

    --msdb.dbo.backupset.description

    FROM msdb.dbo.backupmediafamily

    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

    WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)

    ORDER BY

    msdb.dbo.backupset.database_name,

    msdb.dbo.backupset.backup_finish_date

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

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