SQLTeam.com | Weblogs | Forums

Log Shipping Issue


Dear Experts,
Restore job in log shipping is failing due to a adhoc log backup taken by another dba.we dont have the log backup file with us.
is there a way to fix this issue?



If you restore a full backup, made after the missing log backup, on the standby server with NORECOVERY or STANDBY, the standard sql server log shipping will usually sort itself out.

If you are responsible for backups on this server, you should stress to anyone else who has access that they should only do COPY_ONLY backups.


We have an Sproc that does backups. Only parameters are DB Name, Type ('F'ull, 'D'iff or 'L'og) and an optional comment. We tell contractors / 3rd parties etc. that they MUST use that if they want to take a "just in case" backup, and then we have the Sproc do whatever is necessary for everything else that relies on backups - even if it is just putting the Backup file in a folder of OUR choosing!!


Are you sure that even full backups would break log shipping?
I have read a post from Gail Shaw,it was mentioned that only log backups out of log shipping process would break the log shipping.


It may be that you have a gap in the log sequence. There might be adhoc log backups happening which is breaking the log chain. Do you know where the error raising? If no then you need to setup a job alert set up that you know when a log transfer fails.
To know more please go this one it may be helpful to you http://sqldbakit.blogspot.in/p/log-shipping.html


A full backup will not break log shipping. As you notice the log backup that is not accounted for will. We run full backup regularly with no problems.

If you try copying a full backup with NORECOVERY make sure to remove old log files. Personally I usually just recreate the log shipping process. However, we are a small shop...


Non COPY_ONLY full backups could break a recovery plan based on differential backups.


But diffs aren't part of Log Shipping. Just log backups.



I was trying to indicated that any non COPY_ONLY backups made by anyone else could mess up any recovery plan including, but not limited to, log shipping.