Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

6 Answers

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 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
share|improve this answer
    
+1 for the advice to use sys.databases –  Peter Schofield Oct 3 '11 at 17:49
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Not that I'm aware of. Backup is done locally then rsync-ed offsite a few hours later. –  Andy Davies Oct 3 '11 at 21:36
add comment

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
share|improve this answer
add comment

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/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.