3

We have a Log Shipping scenario where the log Backups are backed up from the Primary server directly to a Third server (using a UNC path), the backups are then copied from here to the Secondary server for restore.

Occasionally we're getting an issue where on attempt to restore at the Secondary server we get the following error:

Error: The media family on device '[path]\[dbname]_20170113011502.trn' is incorrectly formed. SQL Server cannot process this media family.

When I look at the Primary server logs there is nothing to suggest that the backup failed

Log was backed up. Database: [dbname], creation date(time): 2016/06/11(11:55:52), first LSN: 1165941:106198:1, last LSN: 1166536:6235:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'[path]\[dbname]_20170113011502.trn'}).

I'm willing to accept that there might be a disk error or something that's causing the corruption; though I can't see anything in any server logs to suggest network or disk errors.

Three initial questions:

1) If there is a disk write issue for the backup why does SQL Server not see this at backup time?

2) If a trn backup like this fails is there any short cut to keeping the Secondary DB in line that doesn't involve a new full backup and restore from more recent successful log backups. My assumption is that if SQL thinks a log backup was written successfully any transactions in that backup would be missed if we used CONTINUE_AFTER_ERROR or some thing like that.

3) I also assume it's not possible to "redo" a log backup for a particular point in time?

Improve this question

1 Answer 1

Reset to default
1
  1. Tricky to answer without more info.

There are many reasons why there may be a disk issue that doesn't actually cause the backup to fail.

  • Have you got any errors in the log related to disk activity? Have you tested those log backups yourself?

  • Can you try restoring them all manually to a test environment and checking database inegrity after if it succeeds?

  • For that matter, do you dbcc checkdb regularly (apologies if this is obvious, just worth mentioning).

  • It's tricky answering this part of the question without more info. -

Addendum: Researching that error seems to only ever point to a version mis-match, but that seems implausible if it's only happening once and all the other backups usually work fine. Worth checking this and asking the question - are both primary and secondary on exact same versions? How often does this happen? This sqlservercentral goes into this.

This earlier stackexchange post is similar, and suggests the same thing. Ruling out the version problem (which from what you've said I'm assuming it's not), then the other points are pointing to corruption.

Just to check again, how often has this happened and how long have you been using this configuration?

If CheckDB is successful on your database, log shipping has been in place a while and this usually runs fine and this is the first such failure, I might be tempted to put it down to a fluke (I know, not ideal) unless you can find anything more out or someone else weighs in.

  1. Short answer is yes.

Take a differential backup and restore that to the secondary. Brent Ozar has described how to do this.

  1. Even shorter answer - no.

A log backup is a very specific thing, once the backup is complete, the log is different (and ever changing). There only way to do anything point-in-time is with log backups. If you need reading material, Paul Randal has a nice article on Technet that goes a bit into how different backups actually work (and it's fairly accessible/not too long).

Improve this answer
4
  • No obvious disk activity errors. I've tried restoring manually but have the same error as the original question, are you suggesting restoring with errors? Of course! differential backups, haven't used them for a while!
    – MrEdmundo
    Jan 13, 2017 at 10:47
  • @MrEdmundo I've edited my answer to add some more stuff (easier to read than comments), lots of these errors point to version problems, but given what you said I find it unlikely. What is the versions of your systems anyway? Or there's corruption sneaked in somewhere. That differential backup thing has got me out of log shipping hot water once or twice, very useful!
    – Ian_H
    Jan 13, 2017 at 11:01
  • definitely not a versioning issue, I remember seeing this exception when this happened last time. We have had this issue before but our IT Infrastructure guys were able to point this to Veeam Backup & Replication which is currently disabled though if this has happened again the Veeam could be a red herring. I will go back to them and suggest from what we can tell there's nothing we can do bar the CheckDB which we do run regularly but I'll get on checking the results. Thanks
    – MrEdmundo
    Jan 13, 2017 at 12:36
  • @MrEdmundo If you check all that I would be inclined to say it's not exactly a db issue - disk issue/corruption or something like that seem likely. Shame there's no further evidence anywhere. Post again if you do discover anything else.
    – Ian_H
    Jan 13, 2017 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.