If I setup log shipping for a database, and I want to do a manual transaction log backup for some reason, is this transaction log will be any conflict or break with scheduled log shipping transaction log backup, are they using the same log back up chain?
So, the logs in log shipping can be used as point in time recovery. Therefore, secondary log backups seem redundant, and yeah, they could lead to a truncation in the log and could mess with log shipping (depending on how everything is configured and done). You can do COPY_ONLY log backups which won't cause log truncation. Restores from those logs can be mixed & matched from other logs, as long as there's no gap in the transaction chain.
However, again, why? What purpose are the out of band log backups going to fulfill?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 1, 2022 at 3:36 pm
Thanks,
I was just curious if they are using same transaction log backup chain. It looks like they do.
I can think of a scenario, for example my log shipping set the transaction log backup is every 3 hours.
For some reason now my primary server has a data loss, and want to restore to 30 minutes ago, my log shipping will run in 2 hours.
I will just do a manual back up of transaction log, the restore to that point of time 30 minutes ago.
July 1, 2022 at 7:46 pm
Perhaps you could take a differential backup instead of a log backup. That would not interfere with any other backup process (and you wouldn't have to apply any tran logs at all if you needed to use the backup).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 4, 2022 at 5:00 am
A differential won’t work in this scenario as you cannot do a point in time restore with a differential.
So if I understand the timeline
12:00 log backup is taken
12:30 data loss event happens
13:00 you notice the data loss
15:00 would be the next scheduled log backup.
So yes you would need a log backup as soon as you notice the data loss event.
Restore with a stop at time of 12:30 and your done.
However, you need to go and re-evaluate your RPO and RTO, if an event which happened 30 minutes ago is an issue in a 3 hour RPO then the RPO is completely wrong, and you would want to look at performing log backups more frequently.
July 4, 2022 at 5:44 pm
You don't need to do point-in-time recovery if you take a differential at the exact point you want to restore to, just like with a full backup (which was OP's other stated option).
I suspect you could even just do a snapshot but I'm a bit more comfortable in these situations with a diff backup if it doesn't take too long.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply