Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NeilG
Aged Yak Warrior
530 Posts |
 Posted - 2010-03-10 : 09:21:11
|
hope someone can answer a simple question which is just really to put my mind at ease.I'm about the change two finance databases recovery model from simple to full inorder to capture all transactions.My question is, i've taken a backup of both datbases, and I know your able to carry this change out while users are on the system.but is there anything else I should do to prevent any problems? -----------------------------------------------Learning something new on SQL Server everyday. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-10 : 11:14:19
|
| Set up transaction log backups. They're essential in full (or bulk logged) recovery to prevent the log growing to fill the drive.Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
 |
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2010-03-11 : 07:54:04
|
Thanks for the response gilla  The reason i'm actually changing the recovery model is put inplace a backup place for the transaction logI was just wondering if there were any other silly pitfalls that people know of which might catch me out.-----------------------------------------------Learning something new on SQL Server everyday. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-11 : 09:14:50
|
| Just watch the size of your LDF file, and the disk space used by your transaction backups. Can;t think of anything else.I recommend a TLog backup every 15 minutes, and if you can organise it to be copied to A.N.Other server then so much the better - in case your main server dies a death. (If you backup to tape only once a day then Full Recovery Model is no better than Simple for a total disaster (albeit still useful if you have accidental deletion, or a database corruption (e.g. disk controller failure) that does not also destroy the TLog backups on disk. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2010-03-11 : 10:47:15
|
| Excellent, thanks for the adviceSomeone did once tell me that backing up the tran log once a day was adequate enough, but I did wonder apart from being able to go back to a point in time what other advantage you'd get over a full backup once a day.cheers again-----------------------------------------------Learning something new on SQL Server everyday. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-11 : 11:16:33
|
"Someone did once tell me that backing up the tran log once a day was adequate enough"They didn't know enough about it ...If you backup the TLog once a day, at midnight, and then have an error [someone deleted all the customers by mistake] at 16:00 then:You can backup the "tail" of the TLog, and then restore (using last Full backup, and subsequent Tlog backups, in order, and finally the "tail" backup you just made) to, say, 15:59.But if you have a disk controller failure, and the LDF file becomes corrupted and you cannot read it to make the "tail" Tlog backup you are hosed  whereas if you make a TLog backup every 15 minutes and then have disk controller failure at 15:59 you can install a new controller, you are still unable to make the final "tail" TLog backup (because LDF file is damaged beyond repair) but you have a TLog backup from 15:45 ... so you can restore last Full backup, all Tlog backups since, up to and included the one at 15:45. Only 14 minutes data is lost.The other problem with a once-a-day TLog backup is that the LDF has to hold every transaction made in 24 hours. On a busy database it will be huge ... the sum of the sizes of the Tlog backups made every 15 minutes and the once-a-day Tlog backup will be the same (plus any overhead bytes for each 15-minute TLog file), so no real difference there, but the size of the LDF file will be under control  . |
|
|
|
|
|
|
|
|
Changing the revcovery model