FULL Recovery mode changed to BULK LOGGED does that break the t-log chain?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » FULL Recovery mode changed to BULK LOGGED...

FULL Recovery mode changed to BULK LOGGED does that break the t-log chain?

Rate Topic

Display Mode

Topic Options

Author

Message

Ariadne

Posted Wednesday, January 13, 2010 3:15 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 19, 2012 4:00 PM
Points: 100, Visits: 168

Should be a quick question I hope. I've forgotten the answer. If you change a 2005 database from FULL to BULK-LOGGED recovery mode when you do your Index rebuilding and then change it back to FULL, doesn't that break the T-Log backup chain?

Thanks,
Lezza

Post #847245

Jeffrey Williams 3188

Posted Wednesday, January 13, 2010 4:05 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, June 10, 2013 12:13 PM
Points: 4,319, Visits: 9,217

No - changing from FULL to BULK-LOGGED and back again does not break the log chain. Changing to/from SIMPLE breaks the log chain.

See the article I link to in my signature for more information on how to manage your transaction logs.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Post #847254

Kay1632

Posted Wednesday, June 22, 2011 11:42 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:53 AM
Points: 349, Visits: 221

If I change from full to bulk logged on replicated database (published), rebuild the indexes and change back to full.
Will it impact replication? Should I stop any of the agents before it?

I need to do it because of tran. log disk becomes full. BTW I am using SQL 2000 SP3.

Post #1129918

GilaMonster

Posted Wednesday, June 22, 2011 11:46 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 38,028, Visits: 30,334

Replication does not depend in any way on recovery model.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1129921

SequelDBA

Posted Monday, October 15, 2012 7:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:32 PM
Points: 35, Visits: 770

I will be loading several thousand records. If I temporarily change my recovery model from FULL to BULK LOGGED, will I lose the ability to recover to a point in time?

This link seems to indicate that.

[url=http://msdn.microsoft.com/en-us/library/ms191244(v=sql.105).aspx][/url]

KU

Post #1372739

GilaMonster

Posted Monday, October 15, 2012 8:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 38,028, Visits: 30,334

Yes, but just within the log backup interval that contains the bulk operations.

p.s. Please post new questions in a new thread in future. Thanks

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1372749

SequelDBA

Posted Monday, October 15, 2012 8:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:32 PM
Points: 35, Visits: 770

Will do...Thanks Gail!

KU

Post #1372751

« Prev Topic | Next Topic »

Permissions