Scheduled backup task doesn't always backup all databases despite always saying job successful

I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job.

It quits with success every day but some days it quits with success only after backing up a few of databases. It can be different number of databases each time. Most days it successfully backups all databases but sometimes 2 backup successfully, sometimes 5, etc.

I don't see any errors in job history, event viewer or sql server log.

Backups are taking place to a local disk, although the folder is a "junction" to a folder on an expandable storage volume.

OS is Windows 2003 64bit running Sql Server 2008 web edition 64 bit as a virtual machine running on Vmware ESXi 5 host.

Stored Procedure:

ALTER PROCEDURE [dbo].[backup_all_databases] 
@path VARCHAR(255)='c:\backups\'

AS

DECLARE @name VARCHAR(50) -- database name  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 
DECLARE @dbIsReadOnly sql_variant -- is database read_only?
DECLARE @dbIsOffline sql_variant -- is database offline?

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('tempdb')
AND version > 0 AND version IS NOT NULL

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
SET @fileName = @path + @name + '.bak'

SET @dbIsReadOnly = (SELECT DATABASEPROPERTY(@name, 'IsReadOnly')) -- 1 = Read Only
SET @dbIsOffline = (SELECT DATABASEPROPERTY(@name, 'IsOffline')) -- 1 = Offline

IF (@dbIsReadOnly = 0 OR @dbIsReadOnly IS NULL) AND @dbIsOffline =0
BEGIN
    BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
    WAITFOR DELAY '00:00:20'
END

FETCH NEXT FROM db_cursor INTO @name 
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Any suggestions please?

I would add TRY/CATCH blocks to handle errors and log them. The DB could be in single user, being restored or whatever.

Without this, errors can abort in a way that no errors are logged (statement, batch, scope, connection etc)

With TRY/CATCH then everything except for compile or connection aborting errors are logged? but I doubt this is the case.

I'd also use sys.databases which replaces sysdatabases and read more flags:

-- declares etc

BEGIN TRY

    DECLARE db_cursor CURSOR LOCAL READ_ONLY STATIC FORWARD_ONLY FOR  
    SELECT name, state, user_access
    FROM sys.databases 
    WHERE name NOT IN ('tempdb')
     
    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @name, @state, @user_access
    
    WHILE @@FETCH_STATUS = 0   
    BEGIN   

        SET @fileName = @path + @name + '.bak'
        IF @state = 0 AND user_access = 0
        BEGIN
            BEGIN TRY
                BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
            END TRY
            BEGIN CATCH
                -- log but do not rethrow so loop continues
            END CATCH
            WAITFOR DELAY '00:00:20'
        END
        ELSE
           --log user and/or state issues
    
        FETCH NEXT FROM db_cursor INTO @name 
    END   
    
    CLOSE db_cursor   
    DEALLOCATE db_cursor

END TRY
BEGIN CATCH
  -- some useful stuff here
END CATCH

StackExchange Database Administrators Q#6436, answer score: 10