Skip to content

Instantly share code, notes, and snippets.

@wsmelton
Created May 10, 2018 13:37
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save wsmelton/d779f7c0c7ea6d59c14b0ba1ae5df098 to your computer and use it in GitHub Desktop.
Save wsmelton/d779f7c0c7ea6d59c14b0ba1ae5df098 to your computer and use it in GitHub Desktop.
Get backup history details
SET NOCOUNT ON;
SELECT distinct t1.name AS 'DatabaseName'
,(datediff( ss, t3.backup_start_date, t3.backup_finish_date)) AS 'DurationInSeconds'
,t3.user_name AS 'UserResponsible'
,t3.name AS backup_name
,t3.description
,t3.backup_start_date
,t3.backup_finish_date
,CASE
WHEN t3.type = 'D' THEN 'Database'
WHEN t3.type = 'L' THEN 'Log'
WHEN t3.type = 'F' THEN 'FileOrFilegroup'
WHEN t3.type = 'G' THEN 'DifferentialFile'
WHEN t3.type = 'I' THEN 'DiferentialDatabase'
WHEN t3.type = 'P' THEN 'Partial'
WHEN t3.type = 'Q' THEN 'DifferentialPartial'
ELSE t3.type
END AS 'BackupType'
,floor(((t3.backup_size/1024)/1024)) AS 'BackupSizeMB'
,t6.physical_device_name
,CASE
WHEN t6.device_type = 2 THEN 'Disk'
WHEN t6.device_type = 102 THEN 'Disk'
WHEN t6.device_type = 5 THEN 'Tape'
WHEN t6.device_type = 105 THEN 'Tape'
END AS 'DeviceType'
,t3.recovery_model
FROM sys.databases t1
INNER JOIN msdb.dbo.backupset t3
ON (t3.database_name = t1.name )
LEFT OUTER JOIN msdb.dbo.backupmediaset t5
ON ( t3.media_set_id = t5.media_set_id )
LEFT OUTER JOIN msdb.dbo.backupmediafamily t6
ON ( t6.media_set_id = t5.media_set_id )
--WHERE t1.name = ''
--WHERE t3.type = 'D'
--WHERE t1.name = '' AND t3.type = ''
ORDER BY backup_start_date DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment