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'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

share|improve this question
    
What does your spErrorHandling stored proc do? Please post that definition if you can. –  Thomas Stringer Apr 2 '13 at 14:55
    
I provided a link in the post that shows what spErrorHandling does. I will add it to the post. –  sixshift04 Apr 2 '13 at 14:57
    
Are you sure you're in the right database context? Also, do you have the underlying object(s) created (table, etc.)? –  Thomas Stringer Apr 2 '13 at 14:59
2  
What does EXECUTE [Full_Backup] @DB_name = @db_names actually do? Post the code for that as well. –  Max Vernon Apr 2 '13 at 15:03
2  
You may want to check Ola Hallengren's database maintenance scripts for a more robust way of doing this. ola.hallengren.com –  Max Vernon Apr 2 '13 at 15:04
show 3 more comments

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
An easy way to test the cursor is to just output a PRINT statement. For example, replace EXECUTE [Full_Backup] @DB_name = @db_names with PRINT db_names and it should tell you right away if every record is getting processed or not –  Rachel Apr 2 '13 at 19:39
    
It does print out all the db_names. That was one of the first things I tried. It's strange that it will print all the names but not execute the sp for every one. –  sixshift04 Apr 4 '13 at 17:10
add comment

I have an alternative suggestion, applicable if you are running an edition of SQL Server rather than Express.

Use a Maintenance Plan to do the backups on a schedule. SQL Agent will log the scheduled job' s execution results.

share|improve this answer
add comment

i wouldn't use maintenance plans - i don't trust them. ;) However, it looks like you could possibly be getting either an empty string or just a partial result with:

select @backupSetId = position from msdb..backupset where database_name=N'''+@DB_name+''' and backup_set_id

But, yeah - I'd use a print statement and see what is actually being generated.

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.