Sql-server – Backups script completes, but doesn’t back up all dbs

backuperror handlingsql serversql-server-2005

I'm running sql server 2005 and I've made a simple backup script that backs up all the databases on one server. The script always succeeds, but when I go to check the log or the files, I see it has only backed up around half of my databases. I've tried it on multiple servers and it does the same thing. I've checked the application log for the backups in the eventvwr, however, it tells me to check the application log for more details so I can never find the detailed information I need.

I saw this similar question and tried the solution that was recommended to try and catch errors. However, there were no errors thrown when I run the script.

DECLARE @db_names NVARCHAR(1000)

DECLARE db_cursor CURSOR
    FOR select name from sys.databases
            where name not in ('tempdb') 
    order by name

OPEN db_cursor
FETCH NEXT FROM db_cursor into @db_names
WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN TRY

        EXECUTE [Full_Backup] @DB_name = @db_names

    END TRY

    BEGIN CATCH

        EXEC spErrorHandling

    END CATCH

FETCH NEXT FROM db_cursor INTO @db_names
END
CLOSE db_cursor
DEALLOCATE db_cursor

I used this sites example for error handling.

-- Declaration statements
DECLARE @Error_Number int
DECLARE @Error_Message varchar(4000)
DECLARE @Error_Severity int
DECLARE @Error_State int
DECLARE @Error_Procedure varchar(200)
DECLARE @Error_Line int
DECLARE @UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @Time_Stamp datetime

-- Initialize variables
SELECT @Error_Number = isnull(error_number(),0),
@Error_Message = isnull(error_message(),'NULL Message'),
@Error_Severity = isnull(error_severity(),0),
@Error_State = isnull(error_state(),1),
@Error_Line = isnull(error_line(), 0),
@Error_Procedure = isnull(error_procedure(),''),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@Time_Stamp = GETDATE();

-- Insert into the dbo.ErrorHandling table
INSERT INTO dbo.ErrorHandling (Error_Number, Error_Message, Error_Severity, Error_State, Error_Line, 
Error_Procedure, UserName, HostName, Time_Stamp)

SELECT @Error_Number, @Error_Message, @Error_Severity, @Error_State, @Error_Line, 
@Error_Procedure, @UserName, @HostName, @Time_Stamp

Is there a reason why backups fail silently? Is there better error handling I can be doing?

[Full_Backup]

declare @path varchar(200)
    set @path = 'P:\Backups\' + CONVERT(char(10), GetDate(),126) + '_' + @DB_name + '.bak'

declare @backupStr nvarchar(max)
set @backupStr = 'BACKUP DATABASE [' + @DB_name + '] TO  DISK = N''' + @path+ ''' WITH NOFORMAT, NOINIT,  NAME = N'''+@DB_name+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

    declare @backupSetId as int
    select @backupSetId = position from msdb..backupset where database_name=N'''+@DB_name+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'''+@DB_name+''' )
    if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database '''''+@DB_name+''''' not found.'', 16, 1) end
    RESTORE VERIFYONLY FROM  DISK = N''' + @path + ''' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND'

Edited to add the [Full_Backup] sp

Best Answer

The problem may actually be that in using your cursor, you're not encountering errors, but databases are simply getting skipped. I have had situations in the past where a default cursor will skip records. I've never been able to determine the cause, but I have found some ways to handle it better in the code.

One is to put some better definition around your cursor declaration. Since this kind of cursor will always iterate forward, you should declare it as such:

DECLARE db_cursor CURSOR STATIC FAST_FORWARD FOR
SELECT name 
FROM sys.databases
WHERE state_desc = 'ONLINE'
    AND name != 'tempdb'
ORDER name

I have found using the FAST_FORWARD option is much more reliable for iterating each item in a data set. Also, it can have other benefits.

Another approach is to build a WHILE... loop structure to handle this. This does not rely on the cursor engine in SQL Server to handle your logic and can give you better reliability for getting all your databases(this is incomplete code, you will need to add backup/error handling logic to make it work):

DECLARE @work table
(dbid int,
 backupdate datetime,
 errormsg varchar(max));

DECLARE @currdb int

INSERT INTO @work (dbid)
SELECT 
    database_id
FROM
    sys.databases a
WHERE
    state_desc = 'ONLINE'
    AND name != 'tempdb'

WHILE EXISTS (SELECT 1 FROM @work WHERE backupdate IS NULL AND errormsg IS NULL)
BEGIN
    SELECT top 1 @currdb = dbid 
    FROM @work 
    WHERE backupdate IS NULL AND errormsg IS NULL; 

    BEGIN TRY
        --Execute Backup Code, then update @work table
        UPDATE @work
        SET backupdate = GETDATE()
        WHERE dbid = @currdb
    END TRY
    BEGIN CATCH
        --Catch error, put message in @work table
        update @work
        set errormsg=ERROR_MESSAGE()
        where dbid = @currdb;
    END CATCH

END

These are two approaches if you decide to move forward with writing your own scripts for backups. Overall, I would strongly recommend you check out Ola Hallengren's backup scripts. They are highly recommended by members of the community, well documented, and thoroughly tested.

Related Question