maintenance plan, backup All Db'd fails on one

Category: sql server manageability

Question

Weazzellboi on Mon, 18 Mar 2013 02:03:59


Hi There

 Can some one please help me with this? I have a sql 2005 server, I have a maintenance plan that backups up all the DB's on this server. All the DB's get backed up to the same network directory, except one fails . This back up happens daily I will copy in the error code that I receive once the maintenance plan has happened. any help would be appreciated.

NEW COMPONENT OUTPUT

Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042 Report was generated on "Server".

Maintenance Plan: Full Database Backup

Duration: 00:03:58

Status: Warning: One or more tasks failed..

Details:

Execute T-SQL Statement Task (AG-SQL-01) Execute TSQL on Local server connection Execution time out: 0 Task start: 2013-03-18T13:00:43.

Task end: 2013-03-18T13:03:36.

Failed:(-1073548784) Executing the query "BACKUP DATABASE [ExpoPromo] TO  DISK = N'\\\\ag-sbs-01\\SQL_backups\\ExpoPromo_Full.bak' WITH INIT " failed with the following error: "The backup data at the end of "\\\\ag-sbs-01\\SQL_backups\\ExpoPromo_Full.bak" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.

BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.



Replies

Sean Gallardy on Mon, 18 Mar 2013 15:22:45


Hello,

What happens if you test that backup media? Can you run a RESTORE VERIFYONLY and see if it is indeed not good?

-Sean

Sarabpreet Singh Anand on Sat, 23 Mar 2013 04:14:55


That tells me that you are trying to take backup in a file which already exists  - i did noticed you used INIT to clear that before writing anything to that file but if the file or its header is already has some bad sectors\corrupted INIT wont help much.

Try giving another name or just delete that file this will definately help.

Weazzellboi on Mon, 25 Mar 2013 00:41:17


Hi Sean, 

i created a separate maintenance plan, it preforms a full backup as does the original maintenance plan that backs up the other Db's. tested it,and it doesn't fail whilst backing up to the same media.

i might edit the original plan and rename the test backup plan as it is working fine?

your thoughts?

Regards 

Weazzellboi

Weazzellboi on Mon, 25 Mar 2013 19:59:05


hi there, 

 thanks for your advice, i did do a few things and worked towards yours as a last resort , was a n interesting learning curve, but all the same, Thank you and to all the others that helped me out, maintenance plan ran successfully for the first time in months. (don't you just like taking over monitoring something to find that it really wasn't being monitored or managed in the first instance!!! :) )