Difference between Full recovery, Bulk-logged, Simple recovery

Difference between Full recovery, Bulk-logged, Simple recoveryHi,
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@.microsoft.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 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@.microsoft.com...
> > Difference between Full recovery, Bulk-logged, Simple recovery
>
>|||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@.microsoft.com...
> 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
>> 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@.microsoft.com...
>> > Difference between Full recovery, Bulk-logged, Simple recovery
>>