| Author |
Topic |
|
Pace
Constraint Violating Yak Guru
264 Posts |
 Posted - 2005-05-13 : 09:28:43
|
Hey All, I know the strategy varies depending on one of a million reasons but I was wondering what is the best t-log backup strategy... Do I use the WITH INIT command to replace the log so I dont get millions upon millions of logs or do I use the NO_TRUNACTE option?I tried searching on truncate but I couldnt really find much help, can anyone help ?when life hands you lemons, ask for tequila and salt  |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-05-13 : 17:35:13
|
| Yes use the WITH INIT option but make sure that your filename is unique each time, otherwise you won't have the chaing of tlogs. We keep two days of tlogs on disk. Check out my backup tlog routine on my blog for how we delete the files past the retention. We also have full backups on disk that we retain for two days. Do not use the NO_TRUCATE option. According to BOL, this option "Allows backing up the log in situations where the database is damaged.", which is not what you want.Tara |
 |
|
|
Pace
Constraint Violating Yak Guru
264 Posts |
Posted - 2005-05-18 : 06:19:47
|
Thanks Tara, I remember reading your blog over and over when I was starting out  when life hands you lemons, ask for tequila and salt |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-18 : 09:54:15
|
| >> Do I use the WITH INIT command to replace the log so I dont get millions upon millions of logsIf you use the same name then this will overwrite the previous log file and so unles it has been copied somewhere the log backup will be useless.It sounds like maybe you don't need tr log backups so decide that first.Then decide what you are trying to accomplish with the backups.Then it should be obvious how you should proceed.Have a look at http://www.nigelrivett.net/SQLAdmin/BackupAllDatabases.htmlFor an easy way of implementing a backup strategy.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Pace
Constraint Violating Yak Guru
264 Posts |
Posted - 2005-05-18 : 11:01:30
|
Hi nr, I can see exactly what you are explaining and you are completely correct. The only reason I am backing up the t-log is so that it truncates and does not start increasing its size.This may be the complete wrong way to go about this. But I love what I get to do and im sure as I get older ill pick more and more up, with a little (or should that be a lot  ) of help from SQLTeam.comwhen life hands you lemons, ask for tequila and salt |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-05-18 : 11:04:32
|
| Just change your recovery model to SIMPLE then. Right click on your database in EM, properties, go to Options tab. Change it to SIMPLE then click OK. You no longer need to bother with the tlog backups. That's if you don't care about point in time recovery.Tara |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-05-18 : 11:16:46
|
| keep in mind that simple recovery mode limits your recovery options. You will not be able to perform a point in time recovery, you will only be able to recover to the last full backup.-ec |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-18 : 15:05:40
|
| Exactly the same as when you overwrite the tr log backups - unless it was the first tr log backup after the full.A lot of systems don't need tr log backups or point in time recovery at all.A lot of others can cope with a few diffs.People tend to make life hard for themselves by reading a few docs and thinking they have to implement tr log backups and restores just because they are available (and then often forget to secure and test them).Made worse by the default installation leaving the recovery model as full and in a state which will crash the server eventually.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Transaction Log Strategy