Solved

SQL backup started failing

Posted on 2015-01-15
8
60 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 11

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 11

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 11

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 11

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 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

17 Experts available now in Live!

Get 1:1 Help Now