November 26, 2005 at 11:45 am
My book states that you can operate in full recovery mode and switch to bulk-logged mode to speed bulk operations. That raises a question, however. Namely, that when the server switches from bulk-logged mode to full recovery mode, does it automatically backup the transaction log file each time you you switch modes? The reason I ask is that in bulk-logged mode, bulk operations are minimally logged. In full recovery mode, however, you can restore a database to any previous point in time. To allow this functionality, i.e. that of restoring up to any point in time, a transaction log backup would be necessary each time you switch modes. If not, please clarify why not?
November 26, 2005 at 5:39 pm
A table that will help make this understandable is found in Books Online. It's titled "Switching Recovery Models" and here's how you navigate to it:
Contents tab >>
Administering SQL Server >>
Backing Up and Restoring Databases >>
Using Recovery Models >>
Switching Recovery Models
An MSDN library link to it is here: Switching Recovery Models. However, it's not pulling up in my version of Firefox for whatever reason. It is pulling up properly in IE.
As per the chart, to get back point-in-time restoration functionality, you must immediately execute a transaction log backup when switching back to full recovery.
K. Brian Kelley
@kbriankelley
November 27, 2005 at 6:12 am
The table states that when switching from full recovery to bulk-logged mode, no action is required. But I would like to know why since if you want to recover up to the point before the bulk operations are performed on the database, how will you do so if you do not take a log backup before performing any bulk operations in bulk-logged mode?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply