Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure in a query window with a WAITFOR in place just to check. In the \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG the message is:
2011-12-28 07:49:52.43 Backup Error: 3041, Severity: 16, State: 1.
2011-12-28 07:49:52.43 Backup BACKUP failed to complete the command BACKUP DATABASE . Check the backup application log for detailed messages.
Obs: the log actually contains all these spaces and no further info.
Best Answer
Well, it looks like 3041 is the generic "something went boom with the backup" error.
Have you tried just running the backup SQL from SQL Agent without using the wrapping stored procedure? I'm assuming the stored procedure you're using for the backup lives in the master DB (or at least not in the DB you're backing up).
Also, you could use this query to check your backup status (last 7 days). There might be something else going on that you don't know about and that's interfering with your backups.