I am after a clear picture of log shipping and all the references I can find do not explain 1 point.

The source database is approx. 200GB and a full backup is made nightly, with transactional backups every 30 minutes.

My understanding is that the first step in log shipping is a full backup is done, copied to the remote server, then restored, then the transactional backups are automatically copied/restored onto the remote server.This makes sense during the day, but the question is, how does the nightly full backup affect this, when it is too big to copy every night.

Thanks for the info, so is it that when a full backup is done that transactions will appear in the destination server, or for example, if tlog backups are done 07:00 to 21:00 and a full backup is performed at 23:00 that any changes that happened after 21:00 will not appear in the destination version until 07:00 or will appear at 23:00?

That is where I am confused, as I thought that a FULL backup which does not perform a Tlog backup as such, does affect the transactions so there is no tlog backup file to copy and the next tlog backup at 07:00 will only have transactions since the full backup and not since the last tlog backup at 21:00.I guess within log shipping it is intelligent enough to identify transactions in the full backup to copy without a normal tlog bak file.

Hi Sotn,One question I would like to ask is why the log backups are not taken during the period of 21:00 to 7:00 ? I believe that it would be more beneficial and simpler if you take trans log backup every 30mins throughout the 24 hours period. Taking Full backups every night should not affect this log shipping trans backup operation. Full backup is taken by the LS Setup and restored only ONCE at the beginning of this process. Full Backup is not taken automatically daily by the LS setup. It is up to you to decide if a separate Full backup job is needed to be scheduled.

Providing a full backup hasn't occurred since the LS plan broke otherwise the Differential_Base_LSN is incremented and the Diff will no longer be restorable on the secondary.

sotn (2/7/2013)That is where I am confused, as I thought that a FULL backup which does not perform a Tlog backup as such, does affect the transactions so there is no tlog backup file to copy and the next tlog backup at 07:00 will only have transactions since the full backup and not since the last tlog backup at 21:00.I guess within log shipping it is intelligent enough to identify transactions in the full backup to copy without a normal tlog bak file.

sotn (2/7/2013)That is where I am confused, as I thought that a FULL backup which does not perform a Tlog backup as such, does affect the transactions so there is no tlog backup file to copy and the next tlog backup at 07:00 will only have transactions since the full backup and not since the last tlog backup at 21:00.

No, it'll have the transactions since the last log backup at 21:00. Full backups do not and never have truncated the log.

if no activity is performed for 12 hours (overnight) then does not seem make sense to perform 24 tlog backups with no real activity. so that is why we do not have tlog backups 24/7 but of course I can easily change the plan to do this.

I had a misunderstanding about how a full db backup affects the transaction log (hence the original question), so I now know that it has 0 effect on tlogs, thanks.

Therefore then, if we do tlog backups every 30 minutes 24/7 and a full backup nightly, and restore a full backup then leaving the destination DB in standby, we can 'report' against it with only 30 minutes latency, or do we need to kill connections to the destination in order to apply the tlog/see the new data.

One more question, if it takes more than 1 day to copy a backup from source to destination this is not a problem as Log Shipping will bring the destination up to date (i.e. apply over 50 tlog backups) providing all tlog backup files are still available on the source.

if no activity is performed for 12 hours (overnight) then does not seem make sense to perform 24 tlog backups with no real activity. so that is why we do not have tlog backups 24/7 but of course I can easily change the plan to do this.

If that fits your recovery plan then so be it, can you be absolutely sure that no one has made a critical update that may be lost?

sotn (2/8/2013)Therefore then, if we do tlog backups every 30 minutes 24/7 and a full backup nightly, and restore a full backup then leaving the destination DB in standby, we can 'report' against it with only 30 minutes latency, or do we need to kill connections to the destination in order to apply the tlog/see the new data.

Depends on how often the copy and\or restore jobs run. For the copied log backups to be restored the users will need to be disconnected. If the restores happen every 15 or 30 minutes then users will get pretty upset

It's not unreasonable for the LS backup job to run every 15 or 30 minutes and have the copy and restore jobs run only twice a day for example, although in reality the copy job frequency should ideally match the backup to avoid copying lots of files in one go. once they're on the secondary the restore job could run twice a day maybe

sotn (2/8/2013)One more question, if it takes more than 1 day to copy a backup from source to destination this is not a problem as Log Shipping will bring the destination up to date (i.e. apply over 50 tlog backups) providing all tlog backup files are still available on the source.

In an ideal world the full backup is used once to initialise the LS plan, after that the restored t-log backups keep the primary and secondary in sync. That is until someone or something comes along to wreck your LS scenario. Even then, depending on the timeframe and backup regime in place, it is usually possible to fix a broken LS scenario without having to completely re initialise.