[Documentation] List your backups in Wiki format

Something simple I cooked up for documentation. Basically, it looks at the backups made in the past 2 weeks and create a table (in Wiki format). Cut and paste into your wiki page, and done.

BTW - if anyone has code to easily POST this to a web site, I'd be grateful. Ideally I'd set this up to run weekly and show changes in the environment.

Cut and paste the code - Wiki tables require that spacing. Make sure to run in TEXT mode

And yes, I know. The code is crude.

CREATE TABLE #wikilist (id INT IDENTITY, formatting VARCHAR(300))

INSERT INTO #wikilist (formatting)
SELECT distinct 'Server: ' + server_name + '{BR}{| border="1"
! Database !! Type !! Location'
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

INSERT INTO #wikilist (formatting)
--SELECT * FROM
select distinct '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name)))))) AS Location
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 '%:'
GROUP BY '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))))
ORDER BY '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))))

INSERT INTO #wikilist (formatting) VALUES ('|}')
SELECT formatting FROM #wikilist ORDER BY id