My CF VPS+ came with SQL 2005 Workgroup installed with a stored procedure that's run by a scheduled job daily. This procedure is supposed to make a daily backup of all databases on the SQL Server, and rotate them - keeping only the most recent 5 backups.

Unfortunately, there seems to be a problem in that only some of the databases (not all) are actually backed up, but only sometimes. There are no reported errors in the job activity log, or the SQL server log. One of the databased is backed-up reliably, the others sometimes get backed-up, other times they don't.

Has anyone else observed this behavior?

If anyone here is good with T-SQL, have a look at the procedure (in msdb) to see what may be causing the problem. I tried to include the SP here, but the forum won't let me...

My msdb.LS_Backups table contains entries for each backup, all of them listing a "completed" status.

It seems that the database that's ALWAYS backup up is the 1st in an alpha sort; don't know if that matters... I also have database names that use both the '-' and '_' characters; could that be causing problems?

What's strange is that sometimes, the SP will work fine, and backup all databases for all 5 days - other times it won't. When I try to run the job manually, it is just as unpredictable.

I found that if I restart SQL Server, and then manually run the job, that it completes sucessfully. How could this be related?

Any help would be appreciated. Thanks.