How to get backup history details

Many times we need to get backup details.
Below procedure which I am using to get backup info. It might be helpful to you guys also.


CREATE PROC Get_backup_info @db   VARCHAR(256),
                            @type VARCHAR(1) = NULL
AS
    BEGIN
        SELECT bs.database_name,
               database_creation_date,
               backup_finish_date,
               expiration_date,
               position                                 backup_postion_in_file,
               CASE type
                 WHEN 'D' THEN 'Database'
                 WHEN 'I' THEN 'Differential database'
                 WHEN 'L' THEN 'Log'
                 WHEN 'F' THEN 'File or filegroup'
                 WHEN 'G' THEN 'Differential file'
                 WHEN 'P' THEN 'Partial'
                 WHEN 'Q' THEN 'Differential partial'
               END                                      AS backup_type,
               backup_size / ( 1024 * 1024 )            AS backup_size_mb,
               compressed_backup_size / ( 1024 * 1024 ) AS compressed_backup_size_mb,
               bf.physical_device_name,
               bks.is_password_protected,
               bks.is_compressed,
               bs.recovery_model,
               bs.is_snapshot,
               bs.is_readonly,
               bs.is_single_user,
               bs.has_backup_checksums,
               bs.is_damaged,
               bs.is_force_offline,
               bs.is_copy_only
        FROM   msdb..backupset bs
               INNER JOIN msdb..backupmediafamily bf
                   ON bs.media_set_id = bf.media_set_id
               INNER JOIN msdb..backupmediaset bks
                   ON bs.media_set_id = bks.media_set_id
        WHERE  bs.database_name = @db
               AND type = Isnull(@type, type)
        ORDER  BY backup_finish_date
    END



Example
To get backup details of database
EXEC Get_backup_info 'testdb'


To get log backupdetails
EXEC Get_backup_info 'testdb' ,'L'

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts