Solved

SQL backup started failing

Posted on 2015-01-15
8
63 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 13

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 13

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 13

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 13

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

749 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