Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

What is the difference between bulk_logged and simple recovery model?

Which one is minimally logged and what is always a better choice for bulk operations?

suppose i am using select into statement to copy more than millions of rows, which one is optimal to use?

share|improve this question

migrated from Sep 27 '09 at 22:39

This question came from our site for professional and enthusiast programmers.

Belongs on Server Fault. – Richard Sep 26 '09 at 10:41

Bulk logged will be minimally logged, so the transaction log size will be smaller during operation between backups, but the transaction log backups themselves will be larger as they include all the extents modified since the last backup.

Even in bulk logged mode you can perform a point in time restore and bring the back up to the point it crashed.

Simple logging just doesn't permit this, the log will be small and no recovery to a point in time is possible, you will be restoring from your last full backup.

The question is not which is better for your million row load, but which recovery model you need to adhere to.

share|improve this answer

The Bulk Logged Recovery model is almost identical to Full, but the Bulk Logged model will not log INSERT/UPDATE specifics - only the table extents. Simple Recovery Model is the only one that does not require log file maintenance, but its log files can not be used to restore data in the event of loss.

Of the Recovery Models, Simple is the most minimally logged and Bulk Logged is recommended for bulk operations.

Reference: Recovery Model Overview

SELECT INTO is preferred, but that means that data already exists and is accessible.

share|improve this answer
Which one is to be used in my case if no data recovery is needed? Only concern is that the operation must not fail due to log file getting full. – Aamod Sep 26 '09 at 20:15
@Aamod: You want to use Simple if no data recovery is needed and don't need/want to maintain logs. – OMG Ponies Sep 26 '09 at 20:25

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.