Below is a script that I use to find the backup history for all the databases on a single instance. There is an inline ‘WHERE’ clause that is currently commented out that you can add in to filter by database name.

use MSDB
bckset.database_name AS DatabaseName,
bckmdiaset.physical_device_name AS BackupLocation,
CASE WHEN bckset.backup_size <= '10485760' THEN
CAST(CAST(bckset.backup_size/1024 AS INT) AS VARCHAR(14)) + ' ' + 'KB'
CASE WHEN bckset.backup_size <= '1048576000' THEN
CAST(CAST(bckset.backup_size/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'MB'
CAST(CAST(bckset.backup_size/1024/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'GB'
END backupSize,
CAST (bckset.backup_start_date AS smalldatetime) AS StartTime,
CAST (bckset.backup_finish_date AS smalldatetime)FinishTime,
CASE WHEN CAST(DATEDIFF(second, bckset.backup_start_date, bckset.backup_finish_date )AS VARCHAR (4)) <= 60 THEN
CAST(DATEDIFF(second, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Seconds'
CAST(DATEDIFF(minute, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Minutes'
END AS TimeTaken,
CAST(bckset.first_lsn AS VARCHAR(25)) AS FirstLogSequenceNumber,
CAST(bckset.last_lsn AS VARCHAR(25)) AS LastLogSequenceNumber,
CASE bckset.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'Transaction Log'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END AS BackupType,
bckset.server_name As ServerName,
bckset.recovery_model As RecoveryModel,
CASE bckset.is_snapshot
END AS IsSnapshot,
CASE [compatibility_level]
WHEN 60 THEN 'SQL Server 6.0'
WHEN 65 THEN 'SQL Server 6.5'
WHEN 70 THEN 'SQL Server 7.0'
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008'
WHEN 110 THEN 'SQL Server 2012'
END AS CompatibilityLevel,
CONCAT (CAST (software_major_version AS VARCHAR (2)), +'.'+
CAST (software_minor_version as VARCHAR (2)), +'.'+
CAST (software_build_version AS VARCHAR (5))) as SqlVersionNumber
FROM msdb.dbo.backupset bckset
INNER JOIN msdb.dbo.backupmediafamily bckmdiaset ON bckset.media_set_id = bckmdiaset.media_set_id
--WHERE bckset.database_name = 'db_name'-- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date

A few things I want to comment on:

  • I select the top 96 on the basis that this should be a full days worth of backups: 15 minute T-Logs and one Full over the course of 24 hours equates to 96. I’ll fully admit that this is a little bit pedantic…
  • Lines 6-14 calculate the size of the size of the backups based on diving the bytes by 1024. Theoretically any of the following 3 are correct for converting bytes to megabytes:
    1. megabytes=bytes/1000000
    2. megabytes=bytes/1024/1024
    3. megabytes=bytes/1024/1000

however which one is right is a far more contentious debate. As I was using boundaries for bytes to megabytes and gigabytes I felt that dividing by 10241024 was the most correct way in this script.

  • I've included whether the backup is a snapshot or not as I work mainly with Developer and Enterprise edition.
  • To aid the compatibility, I've included info about what version of SQL the backups are running on. As different service packs can impact restoring I've concatenated the version numbers to one readable column.