Sql-server – Sql Server 2008 Backups not quite running

backupsql serversql-server-2008

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

Message

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.

Thanks!
Daniel

Best Answer

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.