[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

[Replication] Document what tables are replicated

I needed this, and rather than do it the easy/long way once, I wrote a piece of code to do it from now on. Feel free to modify it, and if you can get that inner loop to create the #Articles table dynamically, PLEASE let me know. I also decided to use the SPs to try and keep it somewhat portable, and because I wanted to play with the prior post (saving SPs to tables).

Oh yeah, what this does: finds each replicated database, then walks each publication and get a list of the articles in that publication. Then formats it for a wiki, using bullets
(aka
* item 1
* item 2).

You'll have to enable data access on your local machine:
EXEC sp_serveroption [your_server_name] , 'data access' , 'true'

CREATE TABLE #Articles
(
[article id] INT,
[article name] sysname,
[base object] nvarchar(257),
[destination object] sysname null,
[synchronization object] nvarchar(257),
[type] SMALLINT,
[status] TINYINT,
filter nvarchar(257),
[description] nvarchar(255),
insert_command nvarchar(255),
update_command nvarchar(255),
delete_command nvarchar(255),
[creation script path] nvarchar(255),
[vertical partition] BIT,
pre_creation_cmd TINYINT,
filter_clause NTEXT,
schema_option binary(8),
dest_owner sysname null,
source_owner sysname null,
unqua_source_object sysname null,
sync_object_owner sysname null,
unqualified_sync_object sysname null,
filter_owner sysname null,
unqua_filter sysname null,
auto_identity_range INT,
publisher_identity_range INT,
identity_range BIGINT,
threshold BIGINT,
identityrangemanagementoption INT,
fire_triggers_on_snapshot BIT
)

CREATE TABLE #finished_list (id INT IDENTITY(1,1), names VARCHAR(100))

DECLARE
@minid SMALLINT,
@maxid SMALLINT,
@min_database SMALLINT,
@max_database SMALLINT,
@min_publication SMALLINT,
@max_publication SMALLINT,
@sqlstatement VARCHAR(1000),
@db_name sysname,
@publication_name sysname

SELECT identity(int, 1,1) as ID, name INTO #Databases FROM MASTER.dbo.sysdatabases WHERE category > 0 AND NAME <> 'distribution'
SELECT @min_database = MIN(id), @max_database = MAX(id) FROM #Databases

--outer loop - each database that has publications
WHILE @min_database <= @max_database
BEGIN
SET @db_name = null
SELECT @db_name = NAME FROM #Databases WHERE id = @min_database
IF @min_database = 1
SELECT @sqlstatement = 'SELECT identity(int, 1,1) as ID, * INTO ##Publications FROM OPENQUERY( [your_server],''SET FMTONLY OFF {call ' + @db_name + '..sp_helppublication}'')'
ELSE SELECT @sqlstatement = 'insert INTO ##Publications exec ' + @db_name + '..sp_helppublication'
EXEC(@sqlstatement)
INSERT INTO #finished_list (names) select '* ' + @db_name + ''
SELECT @min_publication = MIN(id), @max_publication = MAX(id) FROM ##Publications
--inner loop - each particular article
WHILE @min_publication <= @max_publication
BEGIN
SET @publication_name = NULL
SELECT @publication_name = NAME FROM ##publications WHERE id = @min_publication
INSERT INTO #finished_list (names) SELECT '** ' + @publication_name + ''
SELECT @sqlstatement = 'INSERT INTO #Articles EXEC ' + @db_name + '..sp_helparticle @publication = ''' + @publication_name + ''''
--I can't get this to work - if you can, please share. Probably something involving sp_executesql
-- IF @min_publication = 1
-- SELECT @sqlstatement = 'SELECT identity(int, 1,1) as ID, * INTO ##Articles FROM OPENQUERY( [your_server],''SET FMTONLY OFF {call ' + @db_name + '..sp_helparticle @publication = N''''' + @publication_name +'''''}'')'
-- ELSE SELECT @sqlstatement = 'insert into ##articles EXEC ' + @db_name + '..sp_helparticle @publication = N'''+ @publication_name +''' '
EXEC(@sqlstatement)
INSERT INTO #finished_list (names) SELECT '*** ' + [article name]+ '' FROM #articles ORDER BY [article name]
truncate table #articles
SET @min_publication = @min_publication + 1
END

TRUNCATE TABLE ##publications
SET @min_database = @min_database+1
END

SELECT * FROM #finished_list ORDER BY id
DROP TABLE #Databases
DROP TABLE ##Publications
DROP TABLE #finished_list
DROP TABLE #articles