[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL backup started failing

Posted on 2015-01-15
8
Medium Priority
?
67 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 14

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 14

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 14

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 14

Accepted Solution

by:
Nakul Vachhrajani earned 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

649 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