[Maintenance] Listing your actual backups

(We'll see how well blogspot handles this post.)
Here's a script I've found myself using a lot. It's pretty simple, and probably has a remnant bug or two. Feel free to correct and send my way. (The part I dislike most is the sys.databases vs sysdatabases.)

What it does and why you should use it:
Lists all recent backups, as well as the actual details from the physical files. That last part makes a big difference - more than once I've seen something in the backup tables, but the file's already been overwritten with a newer version. Plus, I have backups on multiple servers and shares, which makes it hard to find quickly. So I wrote this. No more digging through job steps, system tables, and directories.

Enjoy!

/****** Object: StoredProcedure [dbo].[Monitor_BackupStatus] Script Date: 01/24/2008 09:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************************

Name: Monitor_BackupStatus

Creator: Michael Bourgon

Purpose: When run on a server, it will query the backup* system tables to determine
which folders have been used for backups in the last 3 weeks. It
then goes through each directory, looking for .B** files.

Dependencies: for 2000, change from sys.databases to sysdatabases

History: 1.00 First version.
mdb 20070727 1.01 removing backups that reside in root - there are better ways, but I need this NOW.
jth 20071127 1.02 added condition to WHERE clause to ignore directories that start with "VDI_" #1001

Notes: Please feel free to reuse and forward, provided this header is included.
Feel free to email me improvements at (bourgon at gmail dot com)

Future improvements:
Better deal with multiple different files, probably by looking for a unique
string prior to the last underscore.

***********************************************************/
CREATE procedure [dbo].[Monitor_BackupStatus]
as
set nocount on
create table #Folder_List (id int identity primary key, directory varchar(1000))
create table #Listing (id int identity primary key, resultant varchar(1000))
create table #Full_Listing
(
id int identity primary key,
database_name sysname,
backup_folder varchar(1000),
backup_size varchar(17),
last_date smalldatetime,
backup_filename varchar(1000)
)

declare @folder_name varchar(1000)
declare @startid smallint
declare @endid smallint

--Get list of backups made in the last two weeks. Checks all locations of backups, even hand-made
insert into #Folder_List(directory)
select distinct left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name)))))
from msdb.dbo.backupmediafamily backupmediafamily inner join msdb.dbo.backupset backupset on backupset.media_set_id = backupmediafamily.media_set_id
where backup_start_date > getdate()-14
and left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))) not like '%:'
and [physical_device_name] NOT LIKE 'VDI_%' --#1001

--Loop through each directory
select @startid = min(id), @endid = max(id) from #Folder_List
while @startid <= @endid
BEGIN
select @folder_name = NULL
select @folder_name = directory from #Folder_List where id = @startid

truncate table #Listing
insert #Listing exec ('exec master..xp_cmdshell ''dir "' + @folder_name + '"''')

insert into #Full_Listing (database_name, backup_folder, backup_size, last_date, backup_filename)
select right(rtrim(@folder_name),charindex('\', reverse(rtrim(@folder_name)))-1) as database_name,
@folder_name as backup_folder,
substring(resultant, 22, 17) as size,
convert(smalldatetime,substring(resultant,1,20)) as last_date,
substring(resultant, 40, 1000) as backup_filename from #Listing where resultant like '%.b%'

set @startid = @startid + 1
END

--Now match everything up. We match on directory since name can cause incorrect duplicates
select left(db.name, 30) as name, full_list.last_date, full_list.backup_size, left(full_list.backup_filename,50) as backup_filename, left(folder.directory,75) as backup_directory
from sys.databases db
full outer join #Folder_List Folder
on right(rtrim(directory),charindex('\', reverse(rtrim(directory)))-1) = db.name
full outer join #Full_Listing full_list
on folder.directory = full_list.backup_folder
--this is commented out because of multiple backups to the same folder due to filegroups, that don't have a date.
-- and full_list.last_date in (select max(last_date) from #Full_Listing group by backup_folder)
where (db.name <>'tempdb' and db.name not like 'XSD_%')
order by db.name, full_list.last_date, folder.directory

drop table #Folder_List
drop table #Listing
drop table #Full_Listing



set nocount off