Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mattshadbolt
Starting Member
2 Posts |
 Posted - 2009-05-25 : 00:37:58
|
| Hi All,I know there are a bunch of articles/questions over the net trying to answer the question of "do full backups break log shipping?" - but so far they are 50/50 split!So I thought I'd ask once and for all.My situation is fairly basic - I've got a primary/secondary log shipping environment to our DC... with only a 2mb link connecting them. I set it up about five months ago and it hummed along until I realized my nightly full backups weren't working (i'd disabled the maint plan while configuring the log shipping) - so i enabled the maint plan and my log shipping stopped with the "20090524040000.trn' is too recent to apply to the secondary database" error. After some careful thought I realized that the full backup is changing the LSN number and causing it to fail. As my DB is about 50GB I had to travel down to the DC to restage the log shipping. I decided that if i disabled the log shipping copy and restore during the full backup window it should keep the LSN's in order... as far as I was aware it worked. I've just noticed that my logs are now telling me that there has been no restore for 27562 mins - 20 days. So I THINK I had a couple of good weeks where my plan worked, though the logs are incomplete so I can't confirm.It’s really important that we continue to get our nightly BAK's as the log shipping is just for our DR plan, not for users who accidentally delete records etc. Also, we like to use the BAK's for development etc - plus its nice to know we have it on tape every night just in case :)So, can someone definitively tell me if the issue is without nightly full BAK's or something else?Thanks in advance! |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-05-25 : 04:09:43
|
| Full backups do not break log shipping. Full backups do not truncate the transaction log. The only things that cause the log to be truncated are log backups, an explicit truncate (backup log with truncate only) or a switch to simple recovery model.What full backups do is reset the differential base if you're using differential backups.If you're having the error "20090524040000.trn' is too recent to apply", then it means that you've missed a log backup. Make sure that you don't have a job other than the log shipping job that backs the log up. Something I've seen before is maintenance plans that have both a full and a log backup in them. If you have that, the log backup may not be going into the folder to get copied, and then you will be getting this problem.--Gail ShawSQL Server MVP |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-05-25 : 04:18:36
|
| Not a big supporter of maint plans - too easy to have problems with them due to not really knowing what's included.Why not do all the backups using backup database then you can apply the logs yourself (easy to code).Then you'll know when a full and log backup is being taken.A full backup will not affect the log backups (except that you can't take any log backups until the first full backup is taken).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mattshadbolt
Starting Member
2 Posts |
Posted - 2009-05-28 : 21:31:24
|
| Could DB integrity checks cause the log sequence to change? I've checked every maint plan we use and none of them are doing tran backups - all Full Backup.Also, could doing a trans backup on the master & msdb databases cause issues? I'm not shipping those dbs, but i find it strange that my four database log ships stopped working at the same time. This would suggest the issue isn't with maintenance plans on each log shipped db - but an issue with something common to all dbs.Anyway, I'm off to the datacenter to restore and restart shipping - fingers crossed! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-05-29 : 04:24:16
|
| No. CheckDB doesn't break the log chain. Backups are per database and what you do to one does not and can not affect another.The only operations that could break log shipping are:1) A log backups that does not get shipped. Doesn't have to be a job. Could be that someone ran one adhoc for some strange reason. Check the error log2) A log truncation (BACKUP LOG ... WITH TRUNCATE ONLY). This will be reported in the SQL error log3) A switch to simple recovery. This will be reported in the SQL Error log--Gail ShawSQL Server MVP |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-05-29 : 05:05:41
|
| I've seen disk errors cause problems with log backups.It's usually a rougue process or someone doing something manually though.Do you know where the failures occur? You should have an alert set up so that you know when a log transfer fails.If you reduce the log backup interval to say 10 mins and make sure you timestamp the name of the backup file you should be able to find out when a log file is missed and what is happening at the time.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Full Backup breaks Log Shipping