Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Log Shipping Simple question re: full backup involvement Expand / Collapse
Author
Message
Posted Wednesday, February 6, 2013 9:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 9:54 AM
Points: 125, Visits: 415
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.

I hope this makes sense.

TIA.
Post #1416592
Posted Wednesday, February 6, 2013 9:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 40,210, Visits: 36,619
Doesn't affect the log shipping at all. The only time a full backup needs to be copied is when the log shipping's broken and needs to be reset, even then a diff backup's usually easier.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1416599
Posted Thursday, February 7, 2013 8:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 9:54 AM
Points: 125, Visits: 415
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?
Post #1417101
Posted Thursday, February 7, 2013 8:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 40,210, Visits: 36,619
How would those transactions appear at the log shipping secondary if the log backups aren't running?

Log shipping means backup log, copy log, apply log, there's no other factor involved magically copying transactions around



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1417111
Posted Thursday, February 7, 2013 8:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 9:54 AM
Points: 125, Visits: 415
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.
Post #1417122
Posted Thursday, February 7, 2013 9:50 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:20 AM
Points: 38, Visits: 432
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.

Dear Mediators , Please correct me if i'm wrong...
Post #1417204
Posted Thursday, February 7, 2013 9:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:17 PM
Points: 6,490, Visits: 13,968
GilaMonster (2/6/2013)
even then a diff backup's usually easier.

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.

Do you understand the principals of log shipping?


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1417206
Posted Thursday, February 7, 2013 12:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 40,210, Visits: 36,619
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.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1417282
Posted Friday, February 8, 2013 4:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 9:54 AM
Points: 125, Visits: 415
thanks for the all the replies,

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.
Post #1417592
Posted Friday, February 8, 2013 4:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:17 PM
Points: 6,490, Visits: 13,968
sotn (2/8/2013)
thanks for the all the replies,

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.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1417601
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse