is bulk logged recovery model support point in time recovery

Question

is bulk logged recovery model support point in time recovery

dastagiri16

Hall of Fame

Points: 3544
More actions
December 23, 2014 at 11:46 pm

#293260

is bulk logged recovery model support point in time recovery

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Shafat Husain SSCrazy Points: 2754 More actions · Answer 1

Hi,

Point in time recovery is only supported in Full recovery model. So NO, Bulk logged recovery model does not support point in time recovery.

http://msdn.microsoft.com/en-IN/library/ms189275.aspx

Hope it helps...!!!

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 2

Thanks...

i do not understand "workloss exposure" comments on the link..

--these

If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.

Otherwise, no work is lost....

what is the meaning of above..if log damaged..sql server again redo the log into log file?

i do not understand the line "changes since that last backup must be redone"

Shafat Husain SSCrazy Points: 2754 More actions · Answer 3

Hi,

Work Loss Exposure means how much data will be lost in case of DR scenario.

Bulk logged Recovery model has log backup enabled but use minimal logging for Bulk logged operations. So In case you have recently done a bulk logged operation or the log since the last log backup are damaged in that case if the database crashes changes done since last log backup will be lost.

changes since that last backup must be redone.

Above comment means that user will have go redo the changes done post last log backup. SQL Server wont do it.

Hope it Helps...!!!

Kindly mark it as solution if this resolves your Query.

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Yes, Bulk-logged recovery model does support point in time recovery, providing there are no minimally logged operations within the log interval containing the time you are trying to restore to.

Let's say that you take log backups every 15 minutes and you want to restore to 14h10. If there were no minimally logged operations between 14h00 and 14h15 (select into, insert select under some conditions, index rebuilds, bulk loads, etc), then you can restore to 14h10. If there are any, you can only restore to either 14h00 or 14h15.

Generally bulk-logged isn't a recovery model that the DB should be in permanently, the usual use for it is to have the DB in full recovery and switch to bulk logged for data loads or index rebuilds and then switch back to full as soon as the operation is complete.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Shafat Husain (12/23/2014)
Hi,
Point in time recovery is only supported in Full recovery model. So NO, Bulk logged recovery model does not support point in time recovery.
http://msdn.microsoft.com/en-IN/library/ms189275.aspx
Hope it helps...!!!

Ah... you read the following from that article...

Can recover to the end of any backup. Point-in-time recovery is not supported.

... so I don't blame you at all for your answer. The MS-provided article you've cited just isn't 100% correct. Please see Gail's response above.

I will add that I'd rather normally run in the FULL recovery model and have my procs control whether or not I was going to go to BULK LOGGED and then back to FULL so that I make sure that know exactly when precise Point-in-Time restores might not be available because I'd have to restore the full logfile backup due to a minimally-logged action in the file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 6

Thanks Gila...I tested it in bulk logged recovery model..

The PIT recovery is possible...

small doubt is sql server consider the below statement is bulk insert?

insert into pittest values (1,'stlg')

GO

Shafat Husain SSCrazy Points: 2754 More actions · Answer 7

No, Simple insert operations does not come under minimally logged operation.

Here is a list of all the minimally logged operations.

http://technet.microsoft.com/en-us/library/ms191244(v=sql.105).aspx

To mention a few

1. Create index

2. Alter index

3. Bulk Insert .. etc

Hope it Helps..!!

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

Shafat Husain SSCrazy Points: 2754 More actions · Answer 8

Thank you Jeff and Gila for the clarification. :blush:

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 9

sry i missed out mentioned count....after go command

The statement is below

insert into table(values)

go 100

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Shafat Husain (12/25/2014)
Thank you Jeff and Gila for the clarification. :blush:

To be honest, I have Gail to thank, as well. She's one smart lady. A while back and like you, I thought that ANY excursion to BULK LOGGED would make PIT restores impossible. :blush: Of course, the first thing I did after that was to test it to be sure and to know what to do if I ever needed to do such a restore under such conditions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

dastagiri16 (12/25/2014)
sry i missed out mentioned count....after go command
The statement is below
insert into table(values)
go 100

No. That wouldn't be considered to be a "Bulk" process and certainly not a "Minimally Logged" process. It's the same as 100 individual INSERTs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Shafat Husain (12/25/2014)
No, Simple insert operations does not come under minimally logged operation.
Here is a list of all the minimally logged operations.
http://technet.microsoft.com/en-us/library/ms191244(v=sql.105).aspx

Um...

From the very page you linked:

Bulk import operations (bcp, BULK INSERT, and INSERT... SELECT). For more information about when bulk import into a table is minimally logged, see Prerequisites for Minimal Logging in Bulk Import.

Now sure, a single row insert is not going to be minimally logged, but larger insert...select certainly can be, although there are a whole lot of requirements that have to be met first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass