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
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.