April 10, 2003 at 8:44 am
I am looking to implement Log Shipping between our Production SQL Server and a warm standby machine, however I am curious about how the following issue is handled.
I am aware that when you run a full backup the backup file generated will contain all the information in the data file and the transaction log file for the specified database. Also when you perform a transaction log backup, the backup file generated will contain only the transactions which have been recorded since the last backup (full or log).
Considering the scenario of log shipping generating transaction log backups at 30min intervals, shipping them to the standby server and restoring them. This process will obviously have commenced with a full backup from the source server and should maintain a consistent representation of the production system (all be it 30mins behind).
Sample Log Shipping Plan
08:00 Log Shipping Backup
08:30 Log Shipping Backup
09:00 Log Shipping Backup
09:30 Log Shipping Backup
10:00 Log Shipping Backup
etc
Clearly in the above example the Log Shipping transaction log backup file which is produced at 09:30 will contain all transactions between 09:00 and 09:30.
But suppose I take a full backup of the database at 09:15 for the purposes of saving a backup to tape, the full backup file generated will contain all the information in the database data file and all the transactions from the transaction log file up to 09:15.
a) Will the next Log Shipping transaction log file when run at 09:30 only contain transactions which have been performed between 09:15 and 09:30, thus losing 15mins of transactions and breaking the sequence of log backups being restored on the standy server?
b) Will the transaction logs generated after the full backup at 09:15 be able to be used to be restored after any restore of the full backup, to bring any restored system up to date (i.e. Full + Log + Log + Log)?
April 10, 2003 at 9:17 am
As far as log shipping is concerned, full and differential backups have no effect on the Transaction Log backup cycle.
We log ship every 2 minutes between primary and secondary. Full backups are taken weekly, and differential backups daily at the primary, and they don't affect the log shipping process.
To answer your questions specifically:-
a) this log will contain all transactions from 9:00 to 9:30
b) yes
April 10, 2003 at 9:24 am
ian
Thankyou for your reply, clearly from your configuration I can see justification for Log Shipping being unaffected by other backups being taken in parallel to Log shipping.
However, (my question b)) if the 09:30 log shipping backup contains transactions between 09:00 and 09:30 how can it be used in conjuction with a full backup taken at 09:15 since there is clearly an overlap in the transactions contained in each of the files?
April 10, 2003 at 10:07 am
In your given example the database knows that it was backed up at 9:15 and if you were to restore that backup using the no recovery option followed by a transaction log restore, the transaction log restore looks at the completion point of the full backup, and restores only the transactions that were committed following the full backup.
To outline the capabilities of continuous sequence offered by the transaction logs it is good to look at the restore example in BOL. Search in BOL for "Creating a Sequence of Transaction Log Backups" and read the description of the two restore scenarios. I think this will answer your question most effectively.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 10, 2003 at 10:19 am
Paul
Rather than me woffle on about this, have a look at BOL under 'BACKUP LOG'. There's a paragraph entitled 'Creating a Sequence of Transaction Log Backups', which shows interleaving full backups and log backups.
April 10, 2003 at 10:23 am
Thanks DavidB & ian.......(have you guys been talking to each other)???
September 16, 2004 at 12:47 am
In the given scenerio, the Transaction log at 9:30 would contain the highest volume bacuase of the backup operation taken place at 9:15. Is there any way to prevent the trasaction of this backup to be logged cause this is of no impact on the databse as such.
Actually in a typical scenerio with a limited Network resource this particular Trasaction log after backup could gave a serious problem. Any solution for that??
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply