December 14, 2006 at 7:07 am
Hey ,
Can anybody tell me the difference between buk_logged and full recovery models. (Apart from the basic difference like, bluk operations are minimallyed logged). Especially during restoration.
Thanks
December 14, 2006 at 8:47 am
There's a nice table showing the differences in recovery models in BooksOnLine under "Selecting a Recovery Model".
Greg
Greg
December 14, 2006 at 12:31 pm
December 16, 2006 at 7:33 pm
The only major difference other than the one mentioned in your question is that Full is the only recovery model that supports point in time restoration. Bulk logged (and simple) only support restoring to the end of the backup.
December 18, 2006 at 8:21 am
From my understanding, the Bulk-logged model works just like a full recovery model until a bulk operation occurs, then it functions like a simple recovery model. The only advantage I can see is log file should not grow so large during a bulk operation but, to be entirely safe, you do a full backup after the bulk operation. If you are going to do that, I would keep my database in a full recovery model, change it to simple before doing a bulk operation, change it back to the full model and perform a backup.
Steve
December 18, 2006 at 10:36 am
>> change it to simple before doing a bulk operation
Did you mean to say change it to bulk-logged before doing a bulk operation?
Bulk logged recovery does not act like simple recovery during a bulk operation as there may be non-bulk operations ocurring at the same time that are still being fully logged.
Remember that switching to bulk-logged or simple recovery will make the current tran log unable to be restored to a point in time, so if a problem occurs while running in the lesser model that makes the current log unusable, you can only restore to the point of the last backup. So, I would advocate creating a backup before you change the recovery model as well.
I believe that the official recomendtion is:
December 18, 2006 at 12:23 pm
I concur with Robert that switching to bulk-logged recovery instead simple recovery may be better, but I would do a Full Backup for step 5. If you don't, switching back to Full Recovery in step 4 does not gain you anything since the bulk-logged operation could not be recovered. The Full Backup gets you back to being able to do point in time recovery.
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply