November 8, 2010 at 5:30 pm
Hi Everyone,
I have setup log shipping on a Primary Server for all the DB's. The Log shipping has been failing for one particular db, because differential backup is taken on daily basis. I am sure because, when the diff backup has been taken, the LSN chain breaks..i.e..the Last LSN and First LSN does not match..
We take the diff backup and to send them to the client everyday and they also want log shipping to be implemented on their DB. As, the diff backup is breaking log shipping..I am thinking we can send them Tlogs on daily basis instead of sending them differentials...But the client has to restore multiple t-logs per day..which might be a lengthy process..
Is there a better solution for this??
Please let me know..Appreciate your help!!
November 8, 2010 at 5:35 pm
Diff backup should not break log chain. Have you checked the server logs to determine if something else is doing log backups, or if something is altering the recovery mode of the database during the diff backup?
Make sure it's not being set to simple for some reason.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 5:44 pm
I've got to agree with Craig, differential backup shouldn't break log shipping. I've implemented this before without issues. There must be something else going on, like a truncate log.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 8, 2010 at 5:46 pm
I recently ran into an issue where a server's diff backups were failing. DB set to full weekly, diffs daily (test db, so no t-logs). On the third day after the full, diffs would fail. FINALLY tracked it down... SAs implemented volume shadow copying. When it kicks off, it initializes all Volume Shadow Services - including the SQL VSS service. When this kicks off, it invalidates the backup, even if a backup isn't done. Stopping the SQL VSS service, and setting it to Manual, fixed this issue for me. Might be worth taking a look at for you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 8, 2010 at 5:49 pm
there are no t-log backups been taken out side the log shipping, other than the LsBackup job...and the recovery model is set to FULL.
The backup scenario is, we send them Full backup every week, and diff backup everyday....
We also log ship that particular DB on our own DR server too...which is also breaking..
And, all other db's on that same instance..are log shipped successfully...except the one which has diff backup on it....
November 8, 2010 at 5:58 pm
Did it fail after the very first differential backup?
Just to be sure can you post the differential backup code you use?
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 8, 2010 at 6:14 pm
Yes, it started failing after the very first diff backup..and the daily diff backups are taken in a maint. plan.
and here is the code:
BACKUP DATABASE ABC
TO DISK = 'D:\Backups\ABC\ABCDifferential.bak'
WITH INIT, DIFFERENTIAL, NAME = 'ABC_Differential', DESCRIPTION = 'ABC Differential backup'
The Maint. Plan has three tasks...
1) Diff backup (above code)
2) T-Log Backup
3) Full Backup with COPY_ONLY option
November 8, 2010 at 6:49 pm
kiranjanjanam (11/8/2010)
Yes, it started failing after the very first diff backup..and the daily diff backups are taken in a maint. plan.and here is the code:
BACKUP DATABASE ABC
TO DISK = 'D:\Backups\ABC\ABCDifferential.bak'
WITH INIT, DIFFERENTIAL, NAME = 'ABC_Differential', DESCRIPTION = 'ABC Differential backup'
The Maint. Plan has three tasks...
1) Diff backup (above code)
2) T-Log Backup
3) Full Backup with COPY_ONLY option
If the maint plan that's doing the DIFF backup is also doing a T-Log backup, you break the log chain for log shipping. Pull step 2 from the maint plan.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 10, 2010 at 6:38 am
Hi,
So as per converstaion the I am thinking Diffrential backup not make any impact on Log shipping.
Am I right ?
I have implemented logshipping on a VLS DB and currently daily fullbackup is taken.
So i am thinking for following steps or maintenance plan
1) Take diffrential backup Daily
2) Take fullbackup on Weekend
Any Other suggestions expert ?
Thanks
Raj
November 10, 2010 at 7:22 am
kiranjanjanam (11/8/2010)
The Maint. Plan has three tasks...1) Diff backup (above code)
2) T-Log Backup
3) Full Backup with COPY_ONLY option
There's your problem. It's not the diff backup that's breaking the log shipping, it's that log backup that's in there. If you're doing log shipping, all log backups must be done through the log shipping and no extra log backups can be done anywhere else.
I'm curious, why take a diff backup then a full backup. Seems very strange, usually the point of a diff backup is when there's not enough time for a full.
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
November 10, 2010 at 4:38 pm
If you read the posts carefully you'll see they send the Diff backups to the client every night. This is more efficient than sending a full backup every night.
Then they have a full backup for their own usage. More relevant for me would be why do the full every night if they are already doing the differential.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 10, 2010 at 6:22 pm
Thanks guys for your replies...
After seeing the posts, I think its the log backups taken out of log shipping that is breaking the log shipping.
i will communicate with the client and will disable that step.
And I have one more question...Shrinking all the DB's everyweek in a good or bad thing to do? I have a shrink db step in my maint. plan and the maint. plan failed beacuse shrink db and backup's executed at the same time...
any suggestions please????
November 10, 2010 at 6:30 pm
kiranjanjanam (11/10/2010)
Thanks guys for your replies...After seeing the posts, I think its the log backups taken out of log shipping that is breaking the log shipping.
i will communicate with the client and will disable that step.
And I have one more question...Shrinking all the DB's everyweek in a good or bad thing to do? I have a shrink db step in my maint. plan and the maint. plan failed beacuse shrink db and backup's executed at the same time...
any suggestions please????
Under 98% of circumstances, don't shrink your files unless you've just done a massive, one-off, operation. They'll just have to regrow, costing you more time and resources.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 11:24 pm
kiranjanjanam (11/10/2010)
Thanks guys for your replies...After seeing the posts, I think its the log backups taken out of log shipping that is breaking the log shipping.
i will communicate with the client and will disable that step.
And I have one more question...Shrinking all the DB's everyweek in a good or bad thing to do? I have a shrink db step in my maint. plan and the maint. plan failed beacuse shrink db and backup's executed at the same time...
any suggestions please????
After going through lot of post and self test i will suggest please do not shrink file if you are doing then shrink with no truncate option.
Thanks
Rajat
http:/www.indiandotnet.wordpress.com
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply