SMS_SITE_SQL_BACKUP – Status Failed
From the archive (2010). Written for the tools of the time, so some details may be out of date.
I had an issue on one of my primary sites where the SMS_SITE_SQL_BACKUP status was showing up as failing. My backups appeared to be functioning properly however.
I was able to resolve the issue by removing the backup task, watching sitecomp.log for the change, then re-enabling the backup task again. After the backup task re-enabled, the component status showed up successful. I let the backups run on schedule and verified they were successful.
Hope this helps someone else out.