We help IT Professionals succeed at work.

Converting Database from Simple Recovery Model to Full Recovery Model

dstjohnjr
dstjohnjr asked
on
Hello Experts!

I am currently looking to convert one of my production databases in SQL Server 2008 from a Simple Recovery Model to a Full Recovery Model, so that I will have the ability to perform point in time database restore operations.

Aside from performing a full backup on the database prior to converting the recovery model, is there anything else I need to do?

In addition, what other features or practices might I need to install or implement in order to be able to perform point in time database restoration?

Appreciate any guidance at all.  TIA!
Comment
Watch Question

Commented:
If you are changing the recovery model perform your full backup *after* changing the recovery model to full.  That way you have a unbroken transaction chain and your tlog backups before your next full backup will be good.

Make sure to take tlog backups, pre-size your log file to a nice big enough size, perhaps do some log file optimization, do a test restore after you're done on a diff db or server.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

Autogrow on a log file can cause perf issues, so try to presize it as much as you can, a good size might be during peak transaction periods and after a full reindex.  Remember, you must take transaction log backups or your log will grow and that's not good in most situations.

Author

Commented:
Thanks for the reply MrAli!  How often should I perform Tlog backups?  Nightly?  Also, should I continue to perform nightly FULL DB backups or not?

Right now, my log file is 2.3GB in size.  That is after about 1.5 years of activity.  I have never done anything with it.  It is currently set to Autogrow.  Are you saying I should set it to a larger fixed size?  If so, how large?

Thanks again for your reply!
Commented:
Hello,

I'd say keep it the same size since that seems like a fair size, but the problem is you might run into performance issues if it hasn't been maintained, it'll be fragmented internally.  IF your database runs nice and fast though, don't bother fixing it up, since it's slightly technical but not difficult.  Here's a good post on it:


You should backup your transaction logs based on how much data you can afford to lose.  If you can afford to lose an hours worth of data, then take a log backup every hour.  Default behavior I believe is every 15 minutes, which is fine, but note in case of a disaster you have to restore every log backup as well.  Do a test restore once you pick one so you know exactly what to do if disaster strikes.

Author

Commented:
Thanks MrAli!  Again, I appreciate your help!

Also, the URL did not come through on your "good post" reference.  Can you please re-post?  Thanks!
Commented:
Wierd!  Here's the URL again:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

I shortened it with tinyurl here just in case there's some wrapping issue going on:
http://tinyurl.com/59mo9m

If that doesn't work for some reason, google:
Kimberly Tripp VLF

That should bring up the relevant posts.  Good luck!

Author

Commented:
Thanks again for your help MrAli!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.