5

I have several databases that I'd like to move over to FULL recovery mode so we can have point-in-time restore capability. We don't need it on all of our databases, just the ones that are transaction heavy and contain data that is constantly being updated.

I've done lots of research and I fully understand what happens when you switch over to FULL recovery, especially how it pertains to the log file.

What I'm looking for is some suggestions or 'gotchas' that may arise from switching some to the FULL recovery model.

Right now, I plan on doing one database at a time, so I can monitor log file growth and determine the best frequency for log file backups to ensure we don't get into a runaway log file situation. I'm also aware that as soon as we switch to FULL recovery that I need to run a full backup on that database. (I understand that FULL mode won't be truly enabled until we do this).

My plan is also to weekly run a full backup, wipe the log files from the previous week and, essentially 'start over'.

Are there any other considerations or advice that would be helpful. Anything I should watch out for? We run full index rebuilds, DBCC CHECKDB and statistics rebuild once a week too. Are there any potential problems with those operations (maybe switch to BULK_LOGGED for that time period as to not explode the log file?)

Thanks for all your help!

Improve this question

2 Answers 2

Reset to default
5

There are lot of things I would like to say

What I'm looking for is some suggestions or 'gotchas' that may arise from switching some to the FULL recovery model.

As far as I know if you change recovery model of database during maintenance window time or when load is relatively less there wont be any problem. It wont create a situation.

I'm also aware that as soon as we switch to FULL recovery that I need to run a full backup on that database. (I understand that FULL mode won't be truly enabled until we do this).

You can take differential backup also perhaps this would be very helpful if you have big database. anything that links LSN chains would do.See below link it has good information about switching between recovery models

http://msdn.microsoft.com/en-gb/library/ms189272.aspx

My plan is also to weekly run a full backup, wipe the log files from the previous week and, essentially 'start over'.

Stop there, this is not correct approach just because you did a full backup and log backup please don't be in idea that you can safely remove old log files and full backup. Do you have a plan to test old backup by restoring to actually see that in case of disaster this backup files would work. Remember only successful restore guarantees that your backup is totally consistent. Do you have a option to check backup integrity.If not please include it into your backup plan. At least keep 4 day old backup files( this is what I do on local disk) before if you delete backups This strategy is upto you keep backups for time where you are sure they wont be useless if disaster strikes. Sometimes business want only specific data change to which was made couple of days. I also have my database backup up on tape and that tape is stored for 6 months.

(maybe switch to BULK_LOGGED for that time period as to not explode the log file?)

Changing recovery model to bulk logged and doing bulk logged operation would make you loose point in time(PIT) recovery so if you are concerned about PIT recovery dont do that. Instead rebuild index through intelligent script which only rebuild fragmented index like one Is Ola Hallengren index rebuild solution please note that if Index is rebuild with full scan for that index stats is already updated with rebuild process.

If you do heavy DML please break it into batches so as not to explode log files.

Improve this answer
3
  • Thanks for this, this is what I was looking for. Questions on point 3 - If we are doing a complete backup on Sundays why wouldn't we be able to get rid of the old log files as the log chain will be reset once that happens, right? On point 4 - won't that only disallow PIT recovery for the window in which it was set to bulk logged?
    – Kris Gruttemeyer
    Jul 14, 2014 at 18:58
  • For3: You are correct full backup resets LSN but what if you found recent backup is corrupt one before is also corrupt but 2 days backup is consistent if you would not have deleted it you could still save yourself from big disaster I have faced this so sharing it with you. For 4: yes correct but what if disaster happens at that time you should plan for worse and hope for best .
    – Shanky
    Jul 14, 2014 at 19:05
  • Agree, thanks for adding those points and explaining the risk versus reward.
    – Kris Gruttemeyer
    Jul 14, 2014 at 19:27
0

When you are scheduling your index rebuilds, is this during an maintenance window with no application - user activities ? Line of business might need point-in-time ( PIT ) recovery so leave in FULL and monitor; or get exception for this window. Sounds like you have scoped this change OK ! Best luck.

Improve this answer
1
  • Yes, it's during a maintenance window. Sunday mornings, around 2am. I guess my concern with that is log file growth due to the index rebuild. I understand that can be a very log-heavy process. I could always change the recovery mode to SIMPLE or BULK_LOGGED directly beforehand, then change it back and re-initialize FULL mode directly after. Does that make sense?
    – Kris Gruttemeyer
    Jul 14, 2014 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.