2

Following on from my earlier question, I'm trying to set up a combination of Log Shipping and database backup. What I have in place is:

  • Log Shipping set up between my two servers, to run every 5 minutes
  • A (bespoke) process that takes a full backup every night at midnight, and log backups every hour

Log Shipping appears to be working OK; however, I cannot get my backups to run. The "full" backup is fine, but if I try to restore to a "point in time" after a few backups, I'm getting lots of errors similar to:

The log in this backup set beings at LSN xxxxxxx, which is too recent to apply to the database. An earlier log backup that includes LSN yyyyyyy can be restored

Having dug around the logs and history tables, I can see that the "backup" performed by Log Shipping is chipping away at the transaction logs, and presumably "stealing" a chunk of the log, meaning there's a break in the chain of my backup logs.

I've tried changing my backup process to use COPY_ONLY when backing up the logs, but this hasn't made any difference.

Is it simply impossible to combine Log Shipping with regular full/log file backups, or am I missing something?

Improve this question
4
  • Is there any Transaction log backup job running on server where database which is log shipped resides. You can query msdb job history to know when log backups were taken
    – Shanky
    Commented Nov 15, 2014 at 16:32
  • @Shanky - yes: on my "Primary" server, as well as the log shipping I've got a process running that takes a transaction log backup every hour.
    – KenD
    Commented Nov 15, 2014 at 16:44
  • You need to stop that log backup job otherwise your Logshipping will keep on breaking
    – Shanky
    Commented Nov 15, 2014 at 17:07
  • 5
    You're thinking about this backwards IMHO. Think about recovery first (log backups that support your recovery point objective) and then about replicating your data. So think about your log shipping process as using the log backups you're already taking for disaster recovery purposes, not the other way around.
    – Aaron Bertrand
    Commented Nov 15, 2014 at 17:50

1 Answer 1

Reset to default
3

Your log backups are being done by the Log shipping process, that is after all what is there to do. So, you will generally not include log backups in your backup strategy but take into account how often your log shipping process runs.

You would only worry about taking full and/or differential backups against the databases involved in log shipping.

Improve this answer
3
  • What I was trying to achieve was a combination of data replication (which seems to be working), and the ability to restore from a backup to a point in time if required: the idea being I could use the last bak and subsequent transaction logs that were being backup up. Would using a differential backup be a better idea?
    – KenD
    Commented Nov 15, 2014 at 16:49
  • 2
    You would simply utilize the log backups from your log shipping process in order to restore to a point in time.
    – user507
    Commented Nov 15, 2014 at 17:49
  • Perfect - that's cracked it. I've changed my bespoke process: instead of kicking off its own log backups every hour, it just grabs a copy of the .trn's from the log shipping directory instead. So I now have replication AND a bunch of bak and trn files to do restores from. Thanks!
    – KenD
    Commented Nov 16, 2014 at 9:41

Your Answer

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

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