index > SQL Server Disaster Recovery and Availability > backup job quits without any errors

backup job quits without any errors

I have a job which is executed via SQL agent -it attempts to backup each database..

The only problem is that it terminates about 1/2 through (not always at the same spot) - there are about 80 databases on the server

No errors are logged.. Where do I look next ?

The commands executed are as follows:

--------------------------------------------------

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'C:\sql2005backups\nightly\' + @DB_Name + 'Daily' + '.bak'
SET @Backup_Name = @DB_Name + N' backup'
BACKUP DATABASE @DB_Name TO DISK = @Backup_Path WITH INIT
END
FETCH NEXT FROM DB_Cursor INTO @DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

Bruce Baker

Have you tried running the TSQL code from within a management studio or a query analyzer window to see where it fails? Does it run to completion in from the Query Analyzer/Management Studio window?

The other thing that you can do is if you go into the properties of the SQL Server Agent job you can have it put the results of the commands to a text file.

In the SQL Server Management Studio or 2000 Enterprise Manager, go to the properties of the job. Edit the step and click the advanced tab. In the middle of the page there will be a text box called Output File. Just browse to where you would like the output of the commands to reside and indicate a file name.

You will be able to see what errors the SQL Server Agent job is encountering.

Drew

Drew Flint

Are databases being added or removed (detatched/attached) during the backup processing maybe?

If the script quits without errors, does it write your completion message? @@fetch_status is a tri-state value. Either a -1 or a -2 value (any value but 0) would end your loop.

You may want to include more robust checking on that value.

In SQL Server 2005 you could throw a select from sys.dm_exec_cursors at the end of your script to see what the ending status was on the cursor down there with the completion message.

bwunder

thanks - I'll check it out your suggestions

no - no databases are being being detached etc and yes, it does get to the completion message..

Bruce.

Bruce Baker

reply 4