July 16, 2014 at 5:02 am
I have a Db which has log shipping turned on. If I create a separate maintenance plan to do a full back up the database nightly , will this affect the log shipping. Many thanks
July 16, 2014 at 5:10 am
Yes. I believe you can use copy only, which avoids this, but why not just use the LS backups.
July 16, 2014 at 5:12 am
Edward-445599 (7/16/2014)
If I create a separate maintenance plan to do a full back up the database nightly , will this affect the log shipping.
No it won't. Full backups do not have any effect on the transaction log, log backups, the log backup chain or anything else related.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2014 at 5:27 am
Steve Jones - SSC Editor (7/16/2014)
Yes. I believe you can use copy only, which avoids this
Full backups do not break the log chain. Copy only on full backups is solely to avoid resetting the differential base.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2014 at 6:44 am
thanks sorry a little confused so I have log shipping enabled...
If I now want to have nightly backups and t-log backups every 30 minutes.
Do I just set up a separate plan? will that then mean I have two sets t-logs?
July 16, 2014 at 7:39 am
You can't have a second set of transaction log backups. That will absolutely break things. But you can set up full backups as Gail outlines.
"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 16, 2014 at 7:43 am
if you take t logs other than LS its definitely going to break your log backup chain
July 16, 2014 at 8:04 am
Edward-445599 (7/16/2014)
thanks sorry a little confused so I have log shipping enabled...If I now want to have nightly backups and t-log backups every 30 minutes.
Do I just set up a separate plan? will that then mean I have two sets t-logs?
Why should you want to create extra backups. Can't you just (file-)copy the existing backups to multiple places (e.g. one location for logshipping, other location to NAS/tape fof DR purpose)?
You can modify the existing setup to match your requirements for a nightly full backup and transaction-log backup every 30 mins.
July 16, 2014 at 8:40 am
right sorry..
Log shipping is set up and TRN file are being outputted to \\tlogs
If I now set up a maintenance plan to do nightly backups and 30 minute backup of the logs IT WILL BREAK THE SHIPPING?
BUT
If I set a maintenance plan to do nightly backups, I presume the default wizard will appended to the backup set. Can I then in case of a disaster restore the nightly backup and use the log shipped TRN to restore to a point in time?
Sorry all
July 17, 2014 at 4:12 am
Edward-445599 (7/16/2014)
right sorry..Log shipping is set up and TRN file are being outputted to \\tlogs
If I now set up a maintenance plan to do nightly backups and 30 minute backup of the logs IT WILL BREAK THE SHIPPING?
BUT
If I set a maintenance plan to do nightly backups, I presume the default wizard will appended to the backup set. Can I then in case of a disaster restore the nightly backup and use the log shipped TRN to restore to a point in time?
Sorry all
The LogShipping agent backup job for this db must be left in place and be the only process backing up the log. You could setup a full backup nightly to backup to the same folder which I think is what you are asking?
Do not prune the t-log backups in any way, allow the LogShipping job\plan to manage this based on your LS plan settings which were supplied during LS setup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 21, 2014 at 12:12 pm
GilaMonster (7/16/2014)
Steve Jones - SSC Editor (7/16/2014)
Yes. I believe you can use copy only, which avoids thisFull backups do not break the log chain. Copy only on full backups is solely to avoid resetting the differential base.
Ahh, sorry, was thinking you mentioned extra log backups, which are problematic.
Fulls don't matter, as Gail mentioned
July 21, 2014 at 2:23 pm
Steve Jones - SSC Editor (7/21/2014)
GilaMonster (7/16/2014)
Steve Jones - SSC Editor (7/16/2014)
Yes. I believe you can use copy only, which avoids thisFull backups do not break the log chain. Copy only on full backups is solely to avoid resetting the differential base.
Ahh, sorry, was thinking you mentioned extra log backups, which are problematic.
Fulls don't matter, as Gail mentioned
They don't affect log backups but they do affect differential backups 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 21, 2014 at 10:51 pm
Perry Whittle (7/21/2014)
Steve Jones - SSC Editor (7/21/2014)
GilaMonster (7/16/2014)
Steve Jones - SSC Editor (7/16/2014)
Yes. I believe you can use copy only, which avoids thisFull backups do not break the log chain. Copy only on full backups is solely to avoid resetting the differential base.
Ahh, sorry, was thinking you mentioned extra log backups, which are problematic.
Fulls don't matter, as Gail mentioned
They don't affect log backups but they do affect differential backups 😉
Technically, I agree but if you do a full backup to a different drive or folder than where the backups are normally stored at (or even the same folder) and someone deletes it, I believe (haven't tried it in a very long time and don't remember for sure) that can screw up the GUI for doing a restore because MSDB still thinks the file exists.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2014 at 11:48 am
ONLY Full backup and Differential Backup, No T log backup!!!!!!!
July 23, 2014 at 4:12 pm
Jeff Moden (7/21/2014)
Perry Whittle (7/21/2014)
Steve Jones - SSC Editor (7/21/2014)
GilaMonster (7/16/2014)
Steve Jones - SSC Editor (7/16/2014)
Yes. I believe you can use copy only, which avoids thisFull backups do not break the log chain. Copy only on full backups is solely to avoid resetting the differential base.
Ahh, sorry, was thinking you mentioned extra log backups, which are problematic.
Fulls don't matter, as Gail mentioned
They don't affect log backups but they do affect differential backups 😉
Technically, I agree but if you do a full backup to a different drive or folder than where the backups are normally stored at (or even the same folder) and someone deletes it, I believe (haven't tried it in a very long time and don't remember for sure) that can screw up the GUI for doing a restore because MSDB still thinks the file exists.
If you take a log backup to different drive or delete it the same is true, so I don't see your point.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply