locked
Problems with Backup Job RRS feed

  • Question

  • So I have an issue with a diff maintenance plan job.  It's showing that the job fails, but when I check the sql logs I see no error telling me what really happen, only that it failed.  I also went to the folder location and it created a backup file of all the databases selected.  This is the error I pulled from the agent job:

    Message

    Executed as user: Domain\prodacct. ....0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  5:00:00 AM  Progress: 2014-07-05 05:00:03.16     Source: {D8F8D9B6-A0AF-4FAD-83AA-BCAC24830B95}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2014-07-05 05:00:04.67     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'\\pfs05\S...".: 14% complete  End Progress  Progress: 2014-07-05 05:00:04.67     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'\\pfs05\S...".: 28% complete  End Progress  Progress: 2014-07-05 05:00:04.69     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'\\pfs05\S...".: 42% complete  End Progress  Progress: 2014-07-05 05:00:04.69     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'\\pfs05\S...".: 57% complete  End Progress  Progress: 2014-07-05 05:00:04.69     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'\\pfs05\S...".: 71% complete  End Progress  Progress: 2014-07-05 05:00:04.69     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'\\pfs05\S...".: 85% complete  End Progress  Progress: 2014-07-05 05:00:04.69     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'\\pfs05\S...".: 100% complete  End Progress  Progress: 2014-07-05 05:00:06.08     Source: Back Up Database Task      Executing query "BACKUP DATABASE [CTAdmin] TO  DISK = N'\\pfs05\...".: 50% complete  End Progress  Progress: 2014-07-05 05:00:06.14     Source: Back Up Database Task      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress  Progress: 2014-07-05 05:00:08.64     Source: Back Up Database Task      Executing query "BACKUP DATABASE [model] TO  DISK = N'\\pfs05\SQ...".: 50% complete  End Progress  Progress: 2014-07-05 05:00:09.08     Source: Back Up Database Task      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress  Progress: 2014-07-05 05:00:42.22     Source: Back Up Database Task      Executing query "BACKUP DATABASE [msdb] TO  DISK = N'\\pfs05\SQL...".: 50% complete  End Progress  Progress: 2014-07-05 05:00:42.60     Source: Back Up Database Task      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress  Progress: 2014-07-05 05:00:48.21     Source: Back Up Database Task      Executing query "BACKUP DATABASE [Reports] TO  DISK = N'\\pfs05\...".: 50% complete  End Progress  Progress: 2014-07-05 05:00:48.27     Source: Back Up Database Task      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress  Progress: 2014-07-05 05:00:52.43     Source: Back Up Database Task      Executing query "BACKUP DATABASE [TConfiguration] TO  DISK = N'\...".: 50% complete  End Progress  Progress: 2014-07-05 05:00:52.54     Source: Back Up Database Task      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress  Progress: 2014-07-05 05:01:00.24     Source: Back Up Database Task      Executing query "BACKUP DATABASE [Warehouse] TO  DISK = N'\\p...".: 50% complete  End Progress  Progress: 2014-07-05 05:01:00.35     Source: Back Up Database Task      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress  Progress: 2014-07-05 05:01:24.54     Source: Back Up Database Task      Executing query "BACKUP DATABASE [OpsCollection] TO  DISK = N...".: 50% complete  End Progress  Progress: 2014-07-05 05:01:24.80     Source: Back Up Database Task      Executing query "declare @backupSetId as int...  The package execution fa...  The step failed.


    • Edited by Diango Tuesday, July 8, 2014 12:07 AM
    Tuesday, July 8, 2014 12:05 AM

Answers

  • Ok I found out what was wrong.  Something I didn't know previously, you may not always get the exact error if you look at the Agent Job running the maintenance plan.  So something I was told was to look at the Maintenance Plan Logs, so I opened up  the history and finally got the real problem.  The maintenance plan is actually looking to backup a database that no longer exists on that instance, so it's failing do to that.  So the solution was to open up the maintenance plan and just save it.
    • Proposed as answer by Ashwin Menon Wednesday, July 9, 2014 3:30 PM
    • Marked as answer by Diango Wednesday, July 9, 2014 5:00 PM
    Wednesday, July 9, 2014 2:56 PM

All replies


  • Who is the owner of the job ? have you checked the owner ? Is the owner is exist in the server ? or is it removed ? Change the owner and try? many be DBA who created the job might have left the organization  and job might still pointing to that owner, so best practice change it to 'SA' and try... If it is maintanance plan just open the maintenance plan and same once again..

    Raju Rasagounder Sr MSSQL DBA

    Tuesday, July 8, 2014 1:26 AM
  • Yea the job is currently owned by our SA account.
    Tuesday, July 8, 2014 2:28 AM
  • Looks like an issue with maintannace , just restart the agent once and try also make sure to open the maintanance plan once and save again and try...

    Raju Rasagounder Sr MSSQL DBA

    Tuesday, July 8, 2014 3:31 AM
  • Yea the job is currently owned by our SA account.

    Hi,

    Is this job a step in maintenance plan( having many steps) or the maintenance plan has only one step for backup. Can you fin something informative in errorlog


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Tuesday, July 8, 2014 6:47 PM
  • All the databases seem to backup successfully and it even clears out the old files based on the clean up task requirements.  For all intents and purposes the job is doing what is suppose to do, except that it's showing as failed.
    • Edited by Diango Tuesday, July 8, 2014 7:06 PM
    Tuesday, July 8, 2014 7:02 PM
  • Well the maintenance plan has two steps, one for the backups and one for the clean up task.
    Tuesday, July 8, 2014 7:05 PM
  • Well the maintenance plan has two steps, one for the backups and one for the clean up task.

    Right click on job and select step select the maintenance plan and click edit , on job step properties page select advanced make sure you have selected Quit job reporting success when job succeeds other wise it would report as failure even if it succeeds

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Tuesday, July 8, 2014 7:39 PM
  • Yea I took a look at the job and that is set correctly so it's not that.  We ran the query themselves which do the backup jobs, and everything was successful.  
    Tuesday, July 8, 2014 11:48 PM
  • Ok I found out what was wrong.  Something I didn't know previously, you may not always get the exact error if you look at the Agent Job running the maintenance plan.  So something I was told was to look at the Maintenance Plan Logs, so I opened up  the history and finally got the real problem.  The maintenance plan is actually looking to backup a database that no longer exists on that instance, so it's failing do to that.  So the solution was to open up the maintenance plan and just save it.
    • Proposed as answer by Ashwin Menon Wednesday, July 9, 2014 3:30 PM
    • Marked as answer by Diango Wednesday, July 9, 2014 5:00 PM
    Wednesday, July 9, 2014 2:56 PM