• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

SQL backup started failing

I do a complete backup of our servers, but also back up the sql database also.  It has run without fail for several years and last week it started to bomb out.  This is the script that is run each night:  BACKUP DATABASE [REUND] TO  DISK = N'\\backupvm\RE and FE DB backup\REUND' WITH NOFORMAT, INIT,  NAME = N'REUND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

I'm getting an error that says backup data at the end of the XXX is incorrectly formatter.  Backup sets on the media  might be damaged and unusuable.  use restore header....

I've been researching this and did delete the file and tried to rerun the job, but get the same message.  Any ideas on how to fix this??
0
cindyfiller
Asked:
cindyfiller
  • 4
  • 4
1 Solution
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Can you perform a test of media itself (a CHKDSK on the drive if it's a Hard Disk)? I wonder if the drive is failing.
0
 
cindyfillerAuthor Commented:
I'll do that right now.  

It is bombing out from the scheduled job, but if I do a backup right from Management Studio by right clicking on the DB, it works fine.  I actually attached a test backup done that way to another instance of SQL yesterday and it was fine.  It's almost like a setting no longer works or ??
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Ok. Also, instead of backing up using the SSMS, you can try using the query editor to backup the database via the same statement that you have in your nightly job - you may encounter a more verbose error. Also, if it's a severe error, SQL Server would log it to the Event Viewer with detailed information on what went wrong.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
cindyfillerAuthor Commented:
I did the chkdsk and found no issues.  I also tried creating a new folder and that also didn't work.  You mentioned the query editor - I wasn't sure what you were referencing.  BTW, the error message I got from the event viewer.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
It may be that the backup file has become corrupt due to an intermittent network issue or some other I/O issue. Can you try creating a new backup file on a different machine and use the WITH FORMAT specifier to reinitialize the backup file? (Do take a backup of the original first).

Once the backup is successful, you may want to use the RESTORE HEADER WITH VERIFYONLY option to verify the consitency of the file (the current file may show up as corrupt when you try to do this).
0
 
cindyfillerAuthor Commented:
I had originally gone with the event viewer log info but dug a bit deeper into the error listed on the actual SQL job.  After X % it said the specified network name was no longer available.  Normally this runs in the middle of the night while other backups and updates are going on.  So I tried running it this morning early when the systems were quiet and it actually completed.  We are on our University's network and I think they throttle the network back - I'm wondering if that is causing this issue.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
That is correct. Intermittent network issues would be responsible for this error. If SQL Server looses connectivity to  the media (in this case the network drive), the backup file will get corrupted. Maybe the SAN administrators are running their own backups at the time your job is attempting to connect to it? Also, if you can move your job to an early morning time, that would be good.
0
 
cindyfillerAuthor Commented:
We are a small shop and I'm the only one who works on our server technology.  Our university does maintain the network so I've submitted a case to them.  I also moved the backup to a morning time slot.  It works some of the time now so I'll continue working with network services.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now