Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Might be some inspiration here: dba.stackexchange.com/q/6436/630 –  gbn Dec 28 '11 at 14:33
add comment

3 Answers 3

up vote 1 down vote accepted

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.

SELECT  
 CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
 msdb.dbo.backupset.database_name,  
 msdb.dbo.backupset.backup_start_date,  
 msdb.dbo.backupset.backup_finish_date, 
 msdb.dbo.backupset.expiration_date, 
 CASE msdb..backupset.type  
   WHEN 'D' THEN 'Database'  
   WHEN 'L' THEN 'Log'  
 END AS backup_type,  
 msdb.dbo.backupset.backup_size,  
 msdb.dbo.backupmediafamily.logical_device_name,  
 msdb.dbo.backupmediafamily.physical_device_name,   
 msdb.dbo.backupset.name AS backupset_name, 
 msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  
ORDER BY  
  msdb.dbo.backupset.database_name, 
  msdb.dbo.backupset.backup_finish_date 
share|improve this answer
add comment

I dont know much about this, but after googling I found this:

This problem may occur when the internal status flag for a full database backup does not reset after you create the full database backup. If a server experiences this issue, it must be applied with appropriate hotfix as per http://support.microsoft.com/kb/921106

However hot fix installation is a major change so you may not want to apply this if this is not a repetitive symptom.

share|improve this answer
    
It is a repetitive symptom. Thanks very much. I'll see when I can apply this. –  Lynx Kepler Dec 29 '11 at 13:01
add comment

The problem here is the backup file was locked, we need to close the file.

Steps to close the file:

Computer Management-->>System Tools-->Shared Folders-->>Open Files-->(here we can see our back up file)-->>right click-->>Close Open file

Now take backup, if backup doesn't go through then we need stop the services of client scheduler and client Acceptor

Computer Management-->>Services and Applications-->>Services-->>find the client acceptor and schdulers, stop the services go to backup folder and delete the file, start the services back and take a backup

Cheers...!!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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