I'm running a server with SQL 2008, and backing up the database with a "regular maintenance plan". (I'm not an expert by far in configuring SQL Server).
The execution of this plan is automated by a SQL Server Agent Job.

This seems to work, I have my backup files in the hard drive, and every now and then I download them and restore them in my machine and they seem to work.

However, I tried to run the backup manually today before making some big changes to the schema, and it reported it as failed. However, the backup file did end up in the hard drive, and I was able to restore it in my machine too.

So, I went looking for the history of execution of the Agent job, and sure enough, it's been failing every day. However, I have no idea what is wrong. Again, the backups seem to work, but the fact that it's reporting them as failures every day is a little unsettling for me.

This is the best log I could find of the problem, but I have no idea what to make of it.
Any ideas / pointers on where I should start looking?

Date        1/24/2010 9:28:55 AM
Log     Job History (Backup.Backup)

Step ID     1
Server      MyServerName
Job Name        Backup.Backup
Step Name       Backup
Duration        00:00:09
Sql Severity        0
Sql Message ID      0
Operator Emailed        
Operator Net sent       
Operator Paged      
Retries Attempted       0


Executed as user: KVARDEKDU\MyServerName$. 
Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    
Started:  9:28:55 AM  
Progress: 2010-01-24 09:28:58.85     
Source: {8C5D2693-8EF1-4505-95A0-735311CBA89C}      
Executing query "DECLARE @Guid UNIQUEIDENTIFIER      
EXECUTE msdb..sp...".: 100% complete  
End Progress  Progress: 2010-01-24 09:29:00.26     

Source: Back Up Database Task      
Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\Backups\\...".: 100% complete  
End Progress  Progress: 2010-01-24 09:29:03.07     

Source: Back Up Database Task      
Executing query "BACKUP DATABASE [MyDBName] TO  DISK = N'E:\Backup...".: 50% complete  
End Progress  Progress: 2010-01-24 09:29:03.90     

Source: Back Up Database Task      
Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  
End Progress  DTExec: The package execution returned DTSER_FAILURE (1).  

Started:  9:28:55 AM  
Finished: 9:29:04 AM  
Elapsed:  9.047 seconds. 
 The package execution failed.  The step failed.



Look in the main SQL server logs and the history for the maintenance plan itself to find more info. The job history itself likely won't provide you with the best information.

  • I couldn't find anything better than that. Could you please tell me where I can find those logs you refer to? Thanks! – Daniel Magliola Jan 24 '10 at 17:53
  • In SSMS connected to the DB engine, under Management->Maintenance Plans, right click the appropriate plan and hit View History. Also check the SQL server logs under Maintenance->SQL Server Logs, or something to that effect. – phoebus Jan 24 '10 at 18:53
  • Thank you! There I found it. I was backing up "specific databases", and one of those was one I deleted a while ago. Thanks! – Daniel Magliola Jan 24 '10 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.