T-SQL Toolbox – 30 Day Backup History

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,

-David.

Advertisements

Please Comment

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

WordPress.com Logo

You are commenting using your WordPress.com 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