0

We have full backups running every Sunday. Differential every day. Log backups every hour. This is all configured as part of our recovery strategy.

I have been requested to configure log shipping on one of the databases so as to place the db syncronized into the secondary server (read only) for reporting purpose.

Since the log shipping works by taking log backups and shipping then to the other sql server via file share, and my understanding is that a log backup usually truncates the log (unless it is taken with copy only mode). So will log backups happening via the log shipping have any negative impact on my recovery strategy?

For example: Am I right to say that log backup taken by my present jobs will have incomplete log backups because the log shipping will have taken log backups truncating the tlog? Or is it that the log shipping log backups are copy only thus not truncating the tlog?

Improve this question

2 Answers 2

Reset to default
2

Negative impact is your backup chain would be broken because you cannot have 2 different log backup jobs. LS is taking the log backup which will do same stuff as your regular log backup job. So from what we do, we keep one running based on RPO/RTO defined and depending if there is any read only requirement for standby database on DR side.

Improve this answer
4
  • Is there no way to make the log shipping log backups copy only so that it doesn't Truncate the log?
    – variable
    Sep 30, 2021 at 9:01
  • Nope, not that I am aware of.
    – KASQLDBA
    Oct 1, 2021 at 17:41
  • So both tlog backups and log shipping cannot be confirmed together without them interfering?
    – variable
    Oct 8, 2021 at 12:04
  • yes that is true
    – KASQLDBA
    Oct 8, 2021 at 21:58
0

Taken from the internet in response to the question Differential backups breaking Log shipping

I have setup log shipping on a Primary Server for all the DB's. The Log shipping has been failing for one particular db, because differential backup is taken on daily basis. I am sure because, when the diff backup has been taken, the LSN chain breaks..i.e..the Last LSN and First LSN does not match..

We take the diff backup and to send them to the client everyday and they also want log shipping to be implemented on their DB. As, the diff backup is breaking log shipping..I am thinking we can send them Tlogs on daily basis instead of sending them differentials...But the client has to restore multiple t-logs per day..which might be a lengthy process..

Is there a better solution for this??

The answer provided was:

There's your problem. It's not the diff backup that's breaking the log shipping, it's that log backup that's in there. If you're doing log shipping, all log backups must be done through the log shipping and no extra log backups can be done anywhere else.

Your question is:

So will log backups happening via the log shipping have any negative impact on my recovery strategy?

It looks like additional TLOG backups (outside of log shipping) are not supported, but I couldn't find any reference on Microsoft's site so support or refute this statement and my knowledge on log shipping is limited.

Better Solutions/Answers

If you have a support agreement with Microsoft then you might be able to open an advisory case with them. They could then provide you with a working solution.

Alternatively, contact a consultant who is certified for SQL Server.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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