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?