June 18, 2014 at 9:15 am
My boss just asked me to change one of our db from Simple to Full recovery model. I'm not a DBA, just a report writer:-D
I read this http://msdn.microsoft.com/en-us/library/ms189272.aspx
It looks pretty straight forward, however I just want to make sure that it is as simple as simply changing it from simple to full.
Thoughts please. I do not want to screw anything up so I am very cautious to things I'm not familiar with.
***SQL born on date Spring 2013:-)
June 18, 2014 at 9:27 am
Yes, it is that easy to change the recovery model.
However, note that once you have changed the recovery model to FULL, you should configure the regular T-Log backups for that particular database.
Otherwise the transaction log file will start to grow till it completely fills the disk making the database unusable until the space is freed again (by truncating the transaction log).
June 18, 2014 at 9:30 am
Can you point me to any info about configuring the transaction log? Filling the HD sounds bad:crazy:
***SQL born on date Spring 2013:-)
June 18, 2014 at 9:35 am
Hi, yes it's that easy, and yes you should definitely schedule log backups. First take a full backup though, as otherwise your database will be operating in what is known as 'pseudo-Simple' mode (where it looks like Full but it isn't really).
If that sounds daunting, it wasn't meant to; just change your recovery model, take a full backup and then schedule your transaction log backups. (It's also a good idea to think about scheduling a job to clear out the old log files after a certain amount of time, as otherwise they could hang around for ever and take up too much space.
Are you familiar with creating SQL Agent jobs?
June 18, 2014 at 9:36 am
thomashohner (6/18/2014)
Can you point me to any info about configuring the transaction log? Filling the HD sounds bad:crazy:
Search for "managing transaction logs". There's loads of stuff out there.
To add to what Sujeet said, make sure you take a full backup immediately after the switch; your log backups will fail until you do.
John
June 18, 2014 at 9:41 am
Thank you gents so very much. I knew it was best to stop by the forum and ask some questions before doing:-D
***SQL born on date Spring 2013:-)
June 18, 2014 at 9:48 am
Reading material before you make this change.
1) http://qa.sqlservercentral.com/articles/Administration/75461/
2) http://qa.sqlservercentral.com/articles/Administration/64582/
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
June 18, 2014 at 9:50 am
Thanks so much GilaMonster. I actually started reading one of your article right before you posted that.:-D
***SQL born on date Spring 2013:-)
June 18, 2014 at 9:53 am
Be sure to check this out as well: http://ola.hallengren.com/.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply