Hi All,
Quick one I'm sure, I'm having difficulty with a line of my query below.
When executed, this query prints a column named 'BackupSizeGB', of which SQL correctly casts as a decimal - I am happy with the output however i'd like to add the letters 'gb' on to the end.
I've tried converting the decimal or concatenating it to a string but SSMS is not happy about doing so.
Does anyone have any tips for me?
SELECT db.name AS DatabaseName, db.state_desc AS CurrentState,
case when MAX(b.backup_finish_date) is NULL then 'No Backup Completed' else convert(varchar(100),
MAX(b.backup_finish_date)) end AS LastBackupFinishedDateTime,
f.physical_device_name AS FileLocation,
cast(round(sum(cast(bs.backup_size AS Numeric(15,4))/1024/1024/1024),2) as decimal(10,2)) AS BackupSizeGB,
db.recovery_model_desc AS RecoveryModel
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'
LEFT OUTER JOIN msdb.dbo.backupmediafamily f on f.media_set_id=b.media_set_id
JOIN msdb.dbo.backupset bs ON f.media_set_id = bs.media_set_id
WHERE db.database_id NOT IN (2) AND db.database_id NOT IN ('1', '2', '3', '4') AND f.physical_device_name IS NOT NULL --sysdbs are excluded
GROUP BY db.name,f.physical_device_name, db.recovery_model_desc, db.state_desc, bs.backup_size
ORDER BY 2 DESC
Many thanks
Matt
