Solved

SQL backup started failing

Posted on 2015-01-15
8
61 Views
Last Modified: 2015-01-20
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
Comment
Question by:cindyfiller
  • 4
  • 4
8 Comments
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 40551686
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
 

Author Comment

by:cindyfiller
ID: 40551700
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
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 40551715
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
 

Author Comment

by:cindyfiller
ID: 40552250
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 40552879
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
 

Author Comment

by:cindyfiller
ID: 40553646
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
 
LVL 12

Accepted Solution

by:
Nakul Vachhrajani earned 500 total points
ID: 40554768
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
 

Author Comment

by:cindyfiller
ID: 40560008
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now