Difference between Full recovery, Bulk-logged, Simple recovery

Difference between Full recovery, Bulk-logged, Simple recovery

Hi,

1. SIMPLE:-
Transaction log will be cleraed automatically after the checkpoint and hence
backup of transaction log is not possible. So we can
recover the database only until last full database backup

2. BULK LOGGED:-

Transaction log will be cleared only after the log backup but the below
operations are minimally logged.

SELECT INTO.
Bulk load operations (bcp and BULK INSERT).
CREATE INDEX (including indexed views).
Text and image operations

So POINT_IN_TIME recovery isnot possible in BULK LOGGED mode

3. FULL:-

Logs all the activites inside the database and with the help of
transactionlog backup you can recover the database point in time.

NOTE:-

For BULK LOGGED and FULL recovery model ensure that the log backup is
happening (enough room in hard disk), otherwise the
transaction log will grow and will eat the full harddisk space and will
create downtime.

Thanks
Hari
SQL Server MVP


"Joe" <Joe@discussions.microsoft.com> wrote in message
news:79078DFD-2D6B-4D0F-8DA9- 947EC2A22D92@microso
ft.com...
> Difference between Full recovery, Bulk-logged, Simple recovery

If i use full backup, but the point - in -time check box is disable once
restoration

"Hari Prasad" wrote:

> Hi,
>
> 1. SIMPLE:-
> Transaction log will be cleraed automatically after the checkpoint and hen
ce
> backup of transaction log is not possible. So we can
> recover the database only until last full database backup
>
> 2. BULK LOGGED:-
>
> Transaction log will be cleared only after the log backup but the below
> operations are minimally logged.
>
> SELECT INTO.
> Bulk load operations (bcp and BULK INSERT).
> CREATE INDEX (including indexed views).
> Text and image operations
>
> So POINT_IN_TIME recovery isnot possible in BULK LOGGED mode
>
> 3. FULL:-
>
> Logs all the activites inside the database and with the help of
> transactionlog backup you can recover the database point in time.
>
> NOTE:-
>
> For BULK LOGGED and FULL recovery model ensure that the log backup is
> happening (enough room in hard disk), otherwise the
> transaction log will grow and will eat the full harddisk space and will
> create downtime.
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "Joe" <Joe@discussions.microsoft.com> wrote in message
> news:79078DFD-2D6B-4D0F-8DA9- 947EC2A22D92@microso
ft.com... 
>
>
>

Point in time applies to processing the transaction log during restore to a
particular point in time.
You must take a transaction log and then this option will be available.

--
Nik Marshall-Blank MCSD/MCDBA

"Joe" <Joe@discussions.microsoft.com> wrote in message
news:035E79B4-2E80-4198-9FA7- F9DB8307E078@microso
ft.com...
> If i use full backup, but the point - in -time check box is disable once
> restoration
>
> "Hari Prasad" wrote:
>