I am looking at running the following query within a scheduled job weekly:
Select TOP 10
s.database_name,
m.physical_device_name,
CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date)
AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
ORDER BY backup_start_date DESC, backup_finish_date
GO
Not sure what I am doing wrong but I continue to get an error, I have tried running it from within the @query
tag for the sendmail option.
Best Answer
As per the code in your comment:
The reason this won't work is the same reason why this wouldn't work:
You're not taking into account the single quotes in the query string. Something like this should be what you need:
Therefore, your constructed query string should be:
You need to use two single quotes in a string that contains a single quote.