|
Home > Archive > MS SQL Server > September 2005 > Difference between Full recovery, Bulk-logged, Simple recovery
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Difference between Full recovery, Bulk-logged, Simple recovery
|
|
|
| Difference between Full recovery, Bulk-logged, Simple recovery
| |
| Hari Prasad 2005-09-30, 3:23 am |
| 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 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...
>
>
>
| |
| Nik Marshall-Blank \(delete fcom for my email addr 2005-09-30, 3:23 am |
| 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...[color=darkred]
> If i use full backup, but the point - in -time check box is disable once
> restoration
>
> "Hari Prasad" wrote:
>
|
|
|
|
|