Obviously I need to study a lot more. Erik Darling helped me with a great query to pull latest backup information (below) and Pivot on full and incremental. I wanted to add infor4mation if the backup was from native SQL or a 3d party, so in the outer query thought of adding a select to get the "description" from msdb.dbo.backupset but obviously I missed some key point(s) on table querying. Here is Eric original Query
SELECT
M.name,
[Recovery Model] =
M.recovery_model_desc,
[State] =
M.state_desc,
[Last Full Backup] =
FORMAT(ISNULL(M.D, '19000101'), 'dd-MM-yyyy hh:mm'),
[Last Differential Backup] =
FORMAT(ISNULL(M.I, '19000101'), 'dd-MM-yyyy hh:mm')
FROM
(
SELECT
db.name,
db.state_desc,
db.recovery_model_desc,
a.type,
a.backup_finish_date
FROM master.sys.databases AS db
LEFT OUTER JOIN msdb.dbo.backupset AS a
ON a.database_name = db.name
) AS Sourcetable
PIVOT
(
MAX(backup_finish_date)
FOR type IN
(
D,
I
)
) AS M --ostRecentBackup
WHERE name NOT IN
(
N'master',
N'msdb',
N'model',
N'tempdb'
);
My additions that fail
SELECT
M.name,
[Recovery Model] =
M.recovery_model_desc,
[State] =
M.state_desc,
[Last Full Backup] =
FORMAT(ISNULL(M.D, '19000101'), 'dd-MM-yyyy hh:mm'),
[Last Differential Backup] =
FORMAT(ISNULL(M.I, '19000101'), 'dd-MM-yyyy hh:mm'),
(SELECT ( description
FROM msdb.dbo.Backupset
WHERE (a.database_name = db.name) )
FROM
(
SELECT
db.name,
db.state_desc,
db.recovery_model_desc,
a.type
a.backup_finish_date
FROM master.sys.databases AS db
LEFT OUTER JOIN msdb.dbo.backupset AS a
ON a.database_name = db.name
) AS Sourcetable
PIVOT
(
MAX(backup_finish_date)
FOR type IN
(
D,
I
)
) AS M --ostRecentBackup
WHERE name NOT IN
(
N'master',
N'msdb',
N'model',
N'tempdb'
);