Point in time restore in Bulked logged mode

Pop quiz.

Your database is in bulk logged mode. You do a transaction log backup.

Can you restore the log to a point in time between the main backup and the log backup?

Scroll down for the answer

 

 

 

Answer:  It depends

If you don't have any bulk logged operations then you can however the second you have a bulk logged operation you are stuffed, and you have to restore the whole log. So if someone has put a little SELECT INTO statement of a SSIS package that does BULK insert you won't be able to do point in time recovery.

So the lesson learnt is, only switch to BULK logged mode when you don't want to do point in time recovery.

 

---

-

Source Click Here.