transaction log backup for log shipping vs manual backup

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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.


  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • 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.

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply