July 5, 2011 at 11:28 pm
Hi All,
Currently my database is in SIMPLE recovery mode and we configured full & differential backups.
Now we are planning to schedule the Transactional log backups to achive the point in time recovery.
So for doing the above we have to change the Recovery mode from 'Simple' to 'Full' or 'Bulk logged'.
My question here is: Before changing the recovery mode what are Precausions or actions need to be taken? and if i change the recovery mode do i need to restart the SQL Server to effect the changes ?
Thanks in advance.
July 6, 2011 at 12:31 am
No, you do not need to restart SQL Server.
The first thing you need to do when you change your database from SIMPLE recovery model to BULK LOGGED or FULL recovery model is to run a full backup. This will start you log chain that will allow you to do a point in time recovery.
Before doing that, you really need to look at your processing. If there are activities (jobs) that complete minimally logged actions in BULK LOGGED recovery model, the t-log backup run after those actions can only be used for point in time recovery up to the time of the first minimally logged operation. If this is not acceptable, then you need to run using the FULL recovery model.
If this is the case, besure you schedule the t-log backups frequently enough to manage the size of your transaction log, and that you size the log so that it does not need to grow under normal use.
July 6, 2011 at 12:50 am
[font="Times New Roman"]
Hi,
Bulk logged recovery model may not provide point in time recovery if there is any bulk operation executed against the database since the last back up because bulk operation are minimally logged. minimally logging means, in the log file only extent level information is recorded for bulk operation.
There are some standard bulk operations i.e. Create index, Alter index, Select into, Insert into, BCP and Bulk insert. if you don't execute these operations, Bulk logged would be a better chioce in my views.
The advantage which comes Bulk logged recovery model is log fie size grow much slower than Full recovery model.
Let me know if you have any concern.
Warm Regards,
Anil
Infosys
[/font]
July 6, 2011 at 1:19 am
anil_kumar32 (7/6/2011)
The advantage which comes Bulk logged recovery model is log fie size grow much slower than Full recovery model.
The only time the log grows slower in bulk-logged is when you have those bulk operations that you were warning about just before. Otherwise the log usage is the same as for full recovery.
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
July 6, 2011 at 1:34 am
Thanks all for your valuable feedbacks.
July 6, 2011 at 1:42 am
[font="Times New Roman"]
Yup, i missed that point. log file usage for bulk logged and full recovery model are same if there are no bulk operation.
Regards,
Anil Kumar
[/font]
July 6, 2011 at 11:01 am
With a 2T database, I think after switching to Full recovery you could then take a differential to start the log chain. It may be faster than a full, if you took a full recently.
July 6, 2011 at 11:21 am
I was surprised, but a quick test shows that a diff actually does reset the log chain.
Note that I took a full in simple, then added rows, then switched to full mode, took a diff, added a row, took a log, then restored from the full and it worked.
July 6, 2011 at 11:30 am
Steve Jones - SSC Editor (7/6/2011)
I was surprised, but a quick test shows that a diff actually does reset the log chain.
As in restart the log chain after a switch to full recovery, yes. Full or diff will work.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply