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

Question

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?

Answer 1

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

Answer 2

Check for errors after the "backup" command, send your own email out for any errors detected.

This will give you a starting point to see what is going on, and guaranteeing to alert you of any problems until you have the job issue sorted out.

Answer 3

Put an order by on the cursor. I've seen cursors to sys.databases have "issues" when you allow SQL to select the order the data is returned. Ordering by name should be enough.

Answer 4

Is the backup running at the same time as backup to tape or some other process copying or accessing the backup files? If so, I'd bet it's failing to overwrite the file because it's in use. If you have room for multiple backup copies, you could change your proc to add a date stamp to the output file, but then you'll need a cleanup routine.

Answer 5

With the introduction of SQL Server 2005, the cursor loop through sysdatabases and even sys.databases appeared to change so it wasn't reliable - and this change in behaviour could be seen with sp_foreachdb as well.

I found changing the type of cursor helped (I think it was fast forward), but ultimately I switched to solutions such as Ola Hallengren's backup and maintenance solution. Like most things that are critical such as backups, you still need to cross-check all the databases to ensure they are backed up even with these potential solutions - and you obviously did, so well done!

Cursor types: http://msdn.microsoft.com/en-us/library/ms378405(v=SQL.90).aspx

Ola's maintenance solution: http://ola.hallengren.com/

Answer 6

I had the same issue, especially while backing up large DBs.

@@fetch_status is a GLOBAL variable, so it might be altered (set to 0) by another cursor than yours. I solved it by doing the following (in pseudocode):

create a temp table with dbNames
select top 1 in a variable (use order by)
while variable is null
do your thing

set variable = null
delete top 1(use order by)
select top 1 in a variable (use order by)
loop

Answer 7

I have tried to figure out this problem and it seems many times users posted the solution that if you make the cursor declaration insensitive then it starts working. So I tested it and yes it does just make sure you cursor declare is static and it starts working.

The fact for which it fails is - Check your server level cursor threshold setting - if it is configured as -1 then it means all cursors are getting populated synchronously in other words while trying to read cursor key set data is synchronous and all is tried to read at same time. If we change this value to 0 that tells SQL server to do a asynchronous population in simple words cursor can fetch the records while key set will still be populating and you will see that after doing this change at server level you will never miss any database using cursors.

Solutions: Either declare Cursor Static Or Change Server level setting "Cursor Threshold" to 0 from -1.

Thanks, Gaurav Mishra | Senior DBA