SQL Maintenance tips & tricks
MSDB Utility Queries
Each time you execute the T-SQL BACKUP command on a MSSQL Server, the system automatically records a plethora of information.
This includes the start/end time of the backup, the backup type (Log/Data), the file/device the backup was written to, etc., etc.
List the backup history for a specific database
-- List the last 100 Backup HISTORY records for a specific database
DECLARE @dbname varchar(100)
SET @dbname = 'MyDBName' --<== SET Target DBName HERE!!!
SELECT TOP 100
@@servername as Servername
,bs.database_name as DBName
,databasepropertyex(bs.database_name,'Recovery') AS RecoveryModel
,case when bs.type = 'D' THEN 'Database' WHEN bs.type = 'L' THEN 'Log' END AS BackupType
,datediff(mi,bs2.backup_start_date,bs2.backup_finish_date) as Duration
dbo.backupset as bs
JOIN msdb.dbo.backupset as bs2 ON (bs.backup_set_id = bs2.backup_set_id)
JOIN msdb.dbo.backupmediafamily as bmf on (bs2.media_set_id = bmf.media_set_id)
WHERE bs.database_name like @dbname
ORDER BY bs2.backup_start_date DESC