Backup successful but shows failed

Question

Backup successful but shows failed

SkyBVI

SSCoach

Points: 15128
More actions
August 27, 2010 at 6:32 am

#140396

hi,
I am backing up a database on other server. It is backed up nightly.
I get a mail in the morning that it failed, but when I saw the backup on the drive on the other server,
the backup was there.
Why does it say it failed?
I saw the log file viewer of that job it says :-
Message
Executed as user: ***\gpsqlagent. ...ion 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:00:00 PM Progress: 2010-08-26 22:00:01.78 Source: {B5B2FCAF-3864-4F42-A7D6-8047FCE57B5D} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2010-08-26 22:00:01.78 Source: {B5B2FCAF-3864-4F42-A7D6-8047FCE57B5D} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2010-08-26 22:01:55.83 Source: Back Up Database Task Executing query "BACKUP DATABASE [database name] TO DISK = N'\\server name\location".: 50% complete End Progress Progress: 2010-08-26 22:07:21.97 Source: Back Up Database Task Executing query "declare @backupSetId as int select @backupSetId =".: 100% complete End Progress Progress: 2010-08-26 22:07:22.01 Source: {B5B2FCAF-3864-4F42-A7D6-8047FCE57B5D} Execu... The package execution fa... The step failed.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 15 posts - 1 through 15 (of 21 total)

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

check the complete job, it might be having some other steps or there could be something else in your backup job script kind of copying backup to some other location, which failed.

----------
Ashish

SkyBVI SSCoach Points: 15128 More actions · Answer 2

@ ashish

Ya, after the backup, there is maintenance cleanup task, which cleans up the older files. That is the second and last step.

But, where is it mentioned that this step failed?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Jinu43 Ten Centuries Points: 1237 More actions · Answer 3

Can you please check that maintenance plan history and check all step status......

you may be find something........

crazy4sql SSCoach Points: 19590 More actions · Answer 4

expand job and check individual step within job

----------
Ashish

SkyBVI SSCoach Points: 15128 More actions · Answer 5

@ashish

The error which I posted, I got from expanding the job only.

No where it mentions that maintenence cleanup task has failed.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

YTZ SSCommitted Points: 1707 More actions · Answer 6

Please run:

select j.name, h.step_id, h.step_name,h.run_status, run_date,run_time,message

from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id

WHERE j.name ='YourJobName'

run_status:

0 = Failed

1 = Succeeded

2 = Retry

3 = Canceled

SkyBVI SSCoach Points: 15128 More actions · Answer 7

@ YTZ

For both step, the run_status are 0

the error for 1st step i mentioned earlier and the error for the other step is :-

The job failed. The Job was invoked by Schedule 10 (dbname_backup@server_10pm_Schedule_Job_Schedule). The last step to run was step 1 (dbname_backup@server_10pm_Schedule_Job_Step).

Regards
Sushant Kumar
MCTS,MCP

crazy4sql SSCoach Points: 19590 More actions · Answer 8

maintenace task is windws task or sql task?

if windows check application log.

if sql then check if any DTS is configured for that. And check DTS history.

Which version of sql you in?

----------
Ashish

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 9

Check your maintenance plan history log and application event log. Problem could be with a process executed after the backup such as the validation of the backup file.

David

YTZ SSCommitted Points: 1707 More actions · Answer 10

Please run:

SELECT j.name, s.step_id, s.step_name, on_success_action, on_success_action, on_fail_action, on_fail_step_id

FROM msdb.dbo.sysjobsteps s INNER JOIN dbo.sysjobs j ON s.job_id = j.job_id

WHERE j.name ='YourJobName'

on_success_action

tinyint

Action to be performed when a step is executed successfully.

on_success_step_id

int

ID of the next step to execute when a step is executed successfully.

on_fail_action

tinyint

Action to be performed when a step is not executed successfully.

on_fail_step_id

int

ID of the next step to execute when a step is not executed successfully.

OR:

Double click job, see Step --"On Success", "On Failure"

Maybe you set "Go to somewhere" for "On Failure". This is why you see "Success" but job was failed. The Failure was hidden by here.

SkyBVI SSCoach Points: 15128 More actions · Answer 11

@ashish

maintenenance plan is sql task

Im in sql server 2005 enterprise edition

@david-2

maintenance plan history doesnt show any error.

it says 1st step took 7 min and 2nd step 0 secs.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 12

@YTZ

I am getting the output as 1 0 2 0 respectively

for on_success_action

on_success_step_id

on_fail_action

on_fail_step_id

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

YTZ SSCommitted Points: 1707 More actions · Answer 13

Sushant:

which step is for your output of the script? You mentioned there are two steps in your job. There should be two outputs.

YTZ SSCommitted Points: 1707 More actions · Answer 14

Sushant:

Do you have the backup files of before last night? Please compare if there is big different size with last night that the job failed.

You got email because there are alert or another job that check table sysjobhistory showing run_status =0. You saw the backup files are there but maybe not completed.

You mentioned the error message:

BACKUP DATABASE [database name] TO DISK = N'\\server name\location".: 50% complete

Check the backup file size of [database name] for last night and before last night.