Backup Jobs stops

Question

Backup Jobs stops

Tara-1044200

SSCoach

Points: 15951
More actions
January 26, 2010 at 1:52 am

#216081

I have a backup job (Full Backup) which runs every friday at 5pm on all my sql servers using LiteSpeed. Since a while what i observed is my backup jobs on some servers stops at the model database without any error when i check back on monday i have to run it manually from the model database. I do backups order by database name.
This issue is killing me, i didnt find any kind of erro messages on network side or system logs or in sql server logs but i want to prevent this from happening, any ideas?
my thought :
I have a backup script which executes through sql job, to prevent this i would like to do a check on my network drive (backup file location ) where it looks for any database.BAK file after friday 5PM if there is any file before that date the backup should run again looping through all database's before friday 5pm.
Here is my script:
declare @dbname as varchar(80)
declare @msgdb as varchar(80)
declare @dbbkpname as varchar(80)
declare @datepart as char(1)
declare full_cursor CURSOR for select name from master.sys.databases
where state_desc = 'ONLINE' and is_read_only = 0 and name not like 'temp%'
name not like 'test%' and name <> 'tempdb' order by name
open full_cursor
Fetch next from full_cursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
select @dbbkpname='\\OBSTBKP\RevProd\full\' + @dbname + '7.bak'
BEGIN TRY
exec master.dbo.xp_backup_database
@database = @dbname,
@filename = @dbbkpname,
@compressionlevel = 5,
@init = 1,
@logging = 2,
@maxtransfersize= 512000,
@threads = 1
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
FETCH NEXT FROM full_cursor INTO @dbname
END
CLOSE full_cursor
deallocate full_cursor
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Animal Magic SSChampion Points: 13964 More actions · Answer 1

why not as a temporary measure remove the model database from your litespeed backup job and give it its own job. that way at least if it fails its only the model database thats not backed up and not your user databases.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

I dont think that will work in my case because its not just model database sometime it stops at user databases too but very rare which atleast proves that problem is not the model database.

Animal Magic SSChampion Points: 13964 More actions · Answer 3

ah ok, from your origional post it seemed like it was always model that was causing the issue. So now we know its not database specific is there anything in the litespeed logs that indicate why a database couldnt be accessed/backed up? Ive never used litespeed but i know with other products there is sometimes the option of running pre/post scripts, do you have any of these configured of something that may be pausing the job for an indefinate amount of time?

murthykalyani SSCarpal Tunnel Points: 4500 More actions · Answer 4

Why do you trying taking a single database backup through litespeed manually and see what happens?

DavidZahner Ten Centuries Points: 1169 More actions · Answer 5

Is there anything in the SQL log about any of the backups failing? I have had similar issues with SQLSafe and from what I have seen, it’s the xp in use which is not returning errors to SQL correctly for the try…catch block or other cases, it hangs because of the same lack of error handling/ passing between the OS thread and the xp. I have had recent luck using the return code from the xp (exec @rc = master.dbo.xp_backup_database…

if @rc <>0…

but it still doesn’t give you the SQL Error info for the catch block and we usually have to turn up the logging for the backup agent to see what the failure is.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 6

I dont see any kind of error mesgs any where. Can you some one please look into my option above help implementing that.