Backup Report

I was working on one the project and they were looking for a backup report which should specify whether it’s physical or logical and few more details. Whatever data I fetched looks good and I felt the same can be helpful for you guys to pull it as a report. If you need any more additions to it please let me know.

Applies To

SQL Server 2005 and Above

[sql]
SELECT DATENAME([weekday], [bs].[backup_start_date]) [Day Of the Week],
CASE [bmf].[device_type]
WHEN 2
THEN ‘Physical’
WHEN 5
THEN ‘Tape’
WHEN 7
THEN ‘Virtual’
WHEN 7
THEN ‘Permanent Backup’
END [Virtual\Native],
CASE [bs].[type]
WHEN ‘D’
THEN ‘Full’
WHEN ‘I’
THEN ‘Differential’
WHEN ‘L’
THEN ‘Log’
END [BackupType],
[database_name] [DatabaseName],
[bs].[backup_start_date] [StartTime],
CONVERT( VARCHAR(10), DATEDIFF([ss], [bs].[backup_start_date], [bs].[backup_finish_date])) + ‘ seconds’ [BackupDuration],
[bs].[description] [Description],
CASE
WHEN [bs].[last_family_number] = 1
THEN ‘SplitBackup-BackupSet-‘ + CONVERT( VARCHAR(10), [bs].[backup_set_id]) + ‘-File ‘ + CONVERT(VARCHAR(4), [bmf].[family_sequence_number])
ELSE ‘Single File’
END [SplitBackup],
[bmf].[physical_device_name] [BackupDestination],
CONVERT( VARCHAR(15), FLOOR([bs].[backup_size] / [bs].[last_family_number] / 1048576)) + ‘ MB’ [BackupSize],
CASE
WHEN [bs].[backup_size] = [bs].[compressed_backup_size]
THEN ‘Uncompressed’
ELSE ‘Compressed’
END [Compression],
CASE
WHEN [bs].[is_snapshot] = 1
OR [bs].[is_copy_only] = 1
THEN 1
ELSE 0
END [Snapshot\CopyOnly]
FROM [msdb]..[backupset] [bs]
INNER JOIN [msdb]..[backupmediafamily] [bmf] ON [bmf].[media_set_id] = [bs].[media_set_id]
ORDER BY [bs].[backup_start_date] DESC;
[/sql]

BackupReport


Posted

in

by

Tags:

Comments

2 responses to “Backup Report”

  1. ajay Avatar
    ajay

    at the line 25 we are getting syntax error

    1. VidhyaSagar Avatar

      @Ajay – Some weird characters inserted in the script. I corrected it now, it should be fine now.

Leave a Reply

Your email address will not be published. Required fields are marked *