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!
Best Answer
There are lot of things I would like to say
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.
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
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.
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.