Get The Backup History for Databases

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 1024/1024 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.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

1 thought on “Get The Backup History for Databases”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s