2

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

8
  • What does your spErrorHandling stored proc do? Please post that definition if you can. Apr 2, 2013 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, 2013 at 14:57
  • Are you sure you're in the right database context? Also, do you have the underlying object(s) created (table, etc.)? Apr 2, 2013 at 14:59
  • 2
    What does EXECUTE [Full_Backup] @DB_name = @db_names actually do? Post the code for that as well.
    – Hannah Vernon
    Apr 2, 2013 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
    – Hannah Vernon
    Apr 2, 2013 at 15:04

3 Answers 3

2

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.

2
  • 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, 2013 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, 2013 at 17:10
0

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.

0

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.

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .