Full Recovery Model and Bulk Recovery Model.

What is the major difference b/w Full Recovery Model and Bulk Recovery
Model.
Thanks
NOOR
On Thu, 5 Aug 2004 22:24:24 -0700, Noor wrote:

>What is the major difference b/w Full Recovery Model and Bulk Recovery
>Model.
>Thanks
>NOOR
>
Hi Noor,
This is explained in Books Online. Use the index to find "Recovery
models", then select the chapter "Selecting a Recovery Model" for a short
breakdown on the differences. You can follow the links in that article to
find more detailed explanations.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo Kornelis but I wanna ask one thing like Indexes save in Bulk Log
or Full Log or Both ?
Thanks
NOOR
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:s8h6h05841p5816abr8i1i1cao9ltf0us9@.4ax.com...
> On Thu, 5 Aug 2004 22:24:24 -0700, Noor wrote:
>
> Hi Noor,
> This is explained in Books Online. Use the index to find "Recovery
> models", then select the chapter "Selecting a Recovery Model" for a short
> breakdown on the differences. You can follow the links in that article to
> find more detailed explanations.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 6 Aug 2004 02:12:35 -0700, Noor wrote:

>Thanks Hugo Kornelis but I wanna ask one thing like Indexes save in Bulk Log
>or Full Log or Both ?
>Thanks
>NOOR
Hi Noor,
I'm sorry, but I don't understand your question. If you ask again using
different words, I might be able to help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Basically I still don't understand the major difference of Bulk Recovery
Model and Full Recovery Model, can you distiguish in one sentence like what
is the major difference b/w in both.
Thanks
NOOR
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fmk6h05ajk947alrej4fjcd5e1t3gv4hma@.4ax.com... [vbcol=seagreen]
> On Fri, 6 Aug 2004 02:12:35 -0700, Noor wrote:
Log
> Hi Noor,
> I'm sorry, but I don't understand your question. If you ask again using
> different words, I might be able to help.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||There are certain operations that under the right conditions can do what is
referred to as a "Minimally logged" operation. These include operations
such as a Bulk Load (BCP, DTS, BULK INSERT), Create and Reindexing and
SELECT INTO. Normally in "Full" recovery mode any operation is fully
logged. Meaning each and every row or data change is logged to the
transaction log so you can fully recover (either roll back or forward) from
it. When you have a "Minimally logged" operation and the Recovery Model is
set to "Bulk Logged" or "Simple" SQL Server will only log a minimal amount
of information and not the actual data. For instance if you issue a Bulk
Insert and insert 1 million rows in a ""Minimally logged" operation the only
thing that gets logged to the transaction log is the page or extent that was
modified during the operation. So instead of a million rows logged you have
just a few entries. This makes for a much more efficient data load. But
when you backup up the log file it will then copy the entire extent that was
touched in this operation to the backup file resulting in a huge log backup
file. So if you have to recover from after a "Minimally logged" operation
you still can but not to a point in time as in "Full" mode. This is due to
the fact the entire extent is replaced and not just the row or page that was
modified. Certain reindexing commands can do a "Minimally logged" operation
if in "Bulk" or "Simple" mode as well. There is more information in BOL for
these and you want to look up "minimally logged bulk copy" in BOL as well to
see what conditions must be met in order to do a minimally logged load.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:OevS4z7eEHA.3028@.TK2MSFTNGP12.phx.gbl...
> Basically I still don't understand the major difference of Bulk Recovery
> Model and Full Recovery Model, can you distiguish in one sentence like
what[vbcol=seagreen]
> is the major difference b/w in both.
> Thanks
> NOOR
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:fmk6h05ajk947alrej4fjcd5e1t3gv4hma@.4ax.com...
Bulk
> Log
>
|||On Fri, 6 Aug 2004 07:01:15 -0700, Noor wrote:

>Basically I still don't understand the major difference of Bulk Recovery
>Model and Full Recovery Model, can you distiguish in one sentence like what
>is the major difference b/w in both.
>Thanks
>NOOR
Hi Noor,
The major difference in one sentence:
"The Bulk-Logged model provides (...) lower log space consumption for
certain large-scale operations (for example, create index or bulk copy).
It does this at the expense of some flexibility of point-in-time
recovery."
In a few sentences:
"The Bulk-Logged Recovery model provides protection against media failure
combined with the best performance and minimal log space usage for certain
large-scale or bulk copy operations. These operations are minimally
logged:
* SELECT INTO.
* Bulk load operations (bcp and BULK INSERT).
* CREATE INDEX (including indexed views).
* text and image operations (WRITETEXT and UPDATETEXT).
In a Bulk-Logged Recovery model, the data loss exposure for these bulk
copy operations is greater than in the Full Recovery model. While the bulk
copy operations are fully logged under the Full Recovery model, they are
minimally logged and cannot be controlled on an operation-by-operation
basis under the Bulk-Logged Recovery model."
Both these quotes are directly from Books Online. Did you follow my advise
to read up on this subject first?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Andrew and Hugo.
Noor
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7l57h0p2gbd7qpcfrsfdpevogc8idbdeng@.4ax.com... [vbcol=seagreen]
> On Fri, 6 Aug 2004 07:01:15 -0700, Noor wrote:
what
> Hi Noor,
> The major difference in one sentence:
> "The Bulk-Logged model provides (...) lower log space consumption for
> certain large-scale operations (for example, create index or bulk copy).
> It does this at the expense of some flexibility of point-in-time
> recovery."
> In a few sentences:
> "The Bulk-Logged Recovery model provides protection against media failure
> combined with the best performance and minimal log space usage for certain
> large-scale or bulk copy operations. These operations are minimally
> logged:
> * SELECT INTO.
> * Bulk load operations (bcp and BULK INSERT).
> * CREATE INDEX (including indexed views).
> * text and image operations (WRITETEXT and UPDATETEXT).
> In a Bulk-Logged Recovery model, the data loss exposure for these bulk
> copy operations is greater than in the Full Recovery model. While the bulk
> copy operations are fully logged under the Full Recovery model, they are
> minimally logged and cannot be controlled on an operation-by-operation
> basis under the Bulk-Logged Recovery model."
> Both these quotes are directly from Books Online. Did you follow my advise
> to read up on this subject first?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)