I keep a little toolbox of T-SQL scripts handy in Simplenote. There are a couple of plug-ins for my favorite browsers, or smartphones if you have one. I don’t, but that’s a whole other post for a whole other blog. With that, I’m never far from a useful script or two. One that I like to have around, especially when I’m in a new environment is my “30 Days of Backup History” scriptlet. It does just what it says on the label – shows you the backup history for all the backed up databases on the server for the last 30 days. So here it is. Feel free to take this, modify, rearrange, chuck it in a stored procedure, etc… If you repost it somewhere else, I just ask for a token acknowledgement.
/* ADMIN: Backup history for the last 30 days. */ DECLARE @dt datetime = dateadd(dd,-30,getdate()); SELECT DBName = bs.database_name ,BackupType = CASE bs.type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Diff' WHEN 'L' THEN 'TLog' ELSE 'Unknown' END ,DateCompleted = bs.backup_finish_date ,DataSize = bs.backup_size ,UserName = bs.[user_name] ,BackupFile = bmf.physical_device_name FROM msdb.dbo.backupset AS bs INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_set_id = bmf.media_set_id WHERE bs.backup_finish_date > @dt ORDER BY bs.backup_finish_date DESC; GO
Thanks for reading,