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?
Best Answer
There are many reasons why there may be a disk issue that doesn't actually cause the backup to fail.
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.
Take a differential backup and restore that to the secondary. Brent Ozar has described how to do this.
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).