June 11, 2009 at 6:50 pm
configured the logshipping between primary and seconday using full recovery model.
My question :- I do lot of batch updates once a month usually on the last day for salary dispatch. If we change to bulk and then switch back to full, will it impact logshipping??means will it break?
June 11, 2009 at 11:16 pm
Yes, you can't change the recovery model when you do log shipping - keep it full.
The Mass work will not be affected by the log shipping - it async operation.
Oded
June 12, 2009 at 7:32 am
Shouldn't be a problem. Log backups can be taken in both full and bulk logged and can restore fine on another server. Just don't switch to simple or you'll have to reinitialise the whole thing.
It's database mirroring that's restricted to only full recovery.
Test it out somewhere before you try on your production server, just to be safe, but I think you'll be OK.
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
June 12, 2009 at 7:47 am
Gail, I'm little confused here. I think in bulk logged, all bulk insert transactions cant be recovered in case of failure or we cant restore to point in time. If OP means he's doing bulk insert (batch import), will log shipping still be able to restore all transactions on target server?
Thanks for your help!!
June 12, 2009 at 8:01 am
In Bulk logged recovery bulk operations are minimally logged. When the transaction log backup runs, it goes through the database and finds all of the extents that were changed by bulk operations and includes those extents in the transaction log backup. Hence that log backup can be restored to another server without any problem. The sole limitation is that you cannot use STOPAT to restore to a time between log backups.
So, say we have log backups running every 15 min between 10pm and 11pm. In full recovery you could chose to restore to any time at all in that interval. In bulk logged you can only restore the entire tran log backup, so you could recover to 10:15, 10:30, 10:45 or 11:00 but you could not restore to 10:23:45 (for eg)
That's not an issue in log shipping when you do want to restore entire log backups.
Oh, one other thing. In bulk logged if the data file is damaged a tail-log backup cannot be done.
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
June 12, 2009 at 8:15 am
Thanks for the clearing the doubts Gail 🙂
Simultaneously i was also going through http://msdn.microsoft.com/en-us/library/ms190692.aspx which helped too.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply