A question on when to use bulk logged recovery mode

Hi all,
I was hoping that someone could tell me a couple of scenarios where the
bulk logged recovery mode is used and perhaps why?
I'm guessing you might use it just before doing a large data import, but
does that mean you might switch to it before the import and switch back
to full recovery once it's done? Is that a good idea?
What other instances might it be used? Perhaps in a reporting database
for some reason?
Many thanks to anyone who can advise
Kindest Regards
SimonSimon
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
BOL says
In Microsoft SQL ServerT 2000, you can switch between full and bulk-logged
recovery models easily. It is not necessary to perform a full database
backup after bulk copy operations complete under the Bulk-Logged Recovery
model. Transaction log backups under this model capture both the log and the
results of any bulk operations performed since the last backup.

> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
There are some operatuions like SELECT INTO..,CREATE INDEX under BULK
logged recovery mode are minimally loggged.
A disadvantage is that you cannot restore LOG file at point of time
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
> What other instances might it be used? Perhaps in a reporting database for
> some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon|||> I'm guessing you might use it just before doing a large data import, but does that mean yo
u might
> switch to it before the import and switch back to full recovery once it's
done? Is that a good
> idea?
Yes, this brings you the advantage of being in contold of when log backups w
hich includes both log
recards and datapages can occur. Run in full normally, swith down the bulk l
ogged only for this
batch and then up to full again. You are now protected against someone doing
SELECT INTO at 3:15 pm
resuting in not being able to do point in time restore for the following log
backup (just as an
example).
My take on this is that I weigh what bulk logged will atcually contribute. W
ill the operation be
faster (compared to full) to warrant bulk logged? And/or the fact that ldf f
ile is smaller, is it
worth it? this need to be compared to the disadvantages (no point in time re
store for such log
backup, and not being able to do log backup if for instance mdf file is gone
). Also, you need to
think about how larger the following log backup is compared to run in full m
ode (either fewer log
records but also data pages, or only log records, but more of them).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the bu
lk logged recovery mode
> is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but d
oes that mean you might
> switch to it before the import and switch back to full recovery once it's
done? Is that a good
> idea?
> What other instances might it be used? Perhaps in a reporting database for
some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon