Snippet Name: enbbakhist

Tags: helpful,scriptgeneration

Created Date: Oct 1 2021 8:05AM
Last Modified Date: Oct 1 2021 8:05AM

–desc: misspelled version of envbakhist, which gets the backup history for either all db’s(when in master) or the db in current scope

Dependencies: none-standalone code

This snippet depends on whether you are in the master database or a specific database if you’re in the master database gets these backup history for full backups for every database in the system if you have focus in a single database that is not master then the results are limited to just the database in scope so you can see the details of all the backups and were taken for that database

Sample Output:

 
  --desc: misspelled version of envbakhist, which gets the backup history for either all db's(when in master)  or the db in current scope
 
 {C}
 
 SELECT TOP 100
 
 s.server_name,
 
 s.database_name,
 
 --m.physical_device_name,
 
 --CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
 
 --CAST(DATEDIFF(second, s.backup_start_date,
 
 --s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
 
 s.backup_start_date,
 
 CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
 
 --CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
 
 CASE s.[type] WHEN 'D' THEN 'Full'
 
 WHEN 'I' THEN 'Differential'
 
 WHEN 'L' THEN 'Transaction Log'
 
 END AS BackupType,
 
 s.recovery_model
 
 FROM msdb.dbo.backupset s
 
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
 
 WHERE s.database_name = DB_NAME() -- Remove this line for all the database
 
 ORDER BY backup_start_date DESC, backup_finish_date