backup job sometimes fails

Hi

We have a nightly job on a production server which backs up all databases
(about 90 on the server)

Sometimes it'll only backup some of them, yet it reports success.

Our proc to manage the process is below:

Any ideas on what we need to do to make it reliable ?

--

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + '
>>--'
SET @.Backup_Path = N'd:\sql2005backups\nightly\' + @.DB_Name + 'Daily' +
'.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT
print ''
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

Print "Finished backing up the databases"

CLOSE DB_cursor
DEALLOCATE DB_cursor

My suggestion is to look into a 3rd party package for reliable backups of business critical data....SQL 2005 just isn't there yet in this area.

Jeff

|||create a job with the above script and keep the script in step1 of the job and then in the advanced options set an output file so that the output will be written to a text file, which will be used for troubleshooting purposes..............in the step2 you can have the mail step......and see whats the output after the backup completes.......is this box with sql 2005 sp1? if yes better go to SP2 as there are few bugs which are fixed in sp2.........also try to go with maintenance plan and see whats the result if the above option fails....|||

Hi

Thanks for this

We already write the output to a file - it doesn't contain any errors or anything unusual - most strange...

The box is running SQL 2005 sp2.

I could go and buy something like Red-gate backup - but I don't see why I should have to ! Most frustrating..

thanks

Bruce

|||

You don't need a third party solution - SQL native backups were fine and have for years. Third party solutions are the ones that tend to be a bit more problematic. The only problem I can think of that Jeff would have been referring to is maintenance plans. The problem is with maintenance plans, not SQL Server backups.

I'm curious - you are using both single quotes as well as double quotes for print statements? Is that the actual script you are using? What is in the output file? Does it have the name of the database that are skipped or not? What is in the error log? Have you tried running a server side trace during the times you run the backups to see what is actually being executed?

-Sue

|||

Hi Sue

Thanks for replying..

The line with the double-quotes we ended up commenting out.

I also put the backup statement in a 'try catch' as follows below and there was no error logged

No - it doesn't have the name of the databases that are skipped - it just seems to exit without logging any errors - and it's not consistent - some nights it gets to the end.

There is nothing logged in the error logs.... No I haven't tried running a server side trace..

thanks, Bruce.

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases order by name

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + ' >>--'
SET @.Backup_Path = N'd:\sql2005backups\nightly\' + @.DB_Name + 'Daily' + '.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BEGIN TRY
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT ;
END TRY
BEGIN CATCH
Print 'Error -- ' + ERROR_MESSAGE();
END CATCH;
print ''
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

|||

So you need to run a trace during the backups (and for a time prior to them starting) to see more of what is going on. You'll also want to look at the logs prior to and during the backups.

-Sue

|||

Bruce,

I too have something like this. I noticed that this happens when you have many databases needs to be backed up.

What we did here is to split this backup jobs into multiple steps. Each Job will backup few databases may be 50.

Try this and let me know.

Thanks

Shilpi

|||Can you check whether the existing backup file is in use? If possible alter the script to append date at the end of backup filename. Since we are backingup nearly 88 databases but didnt find any issues.

In which account is the job running and does the account has backup operator privilege to those failed db's?