| Author |
Topic |
|
jahnzee
Starting Member
4 Posts |
 Posted - 2008-02-11 : 15:14:28
|
| My client has setup a SQL Server 2000 installation in which there are 2 maintenance plans that run at 2:00 AM everyday. One maintenance plan backs up all user databases (i.e. not master, msdb, etc.) to disk and the other backs up all transaction logs (except master, msdb, etc.) to disk. Around 4:00 AM, the backup files are backed up to tape.My question is: Is it necessary to back up the transaction log in addition to the database, if the database is fully backed up and the log is backed up at about the same time? My memory is a bit cloudy, but it seems like if the recovery option is set to Full, then backing up the log at the same time as the full database backup seems redundant. The reason I ask is that the client has a whole lot of data and they're running out of tape space. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-11 : 23:01:26
|
| Yes have to do log backup, not once a day but at least hourly. |
 |
|
|
jahnzee
Starting Member
4 Posts |
Posted - 2008-02-14 : 09:14:00
|
quote:
Originally posted by rmiao
Yes have to do log backup, not once a day but at least hourly.
I don't mean to be rude, but your answer sounds like something out of a textbook (e.g. the one I have in front of me).Further, one does not *have to* do a log backup at least hourly, because I was able to restore a database without doing hourly log backups (although I did lose some data because of doing so).I'm going to figure it out on my own by restoring a database without a transaction log backup to see what happens. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-16 : 00:52:46
|
| Textbook is wrote by experienced people, we backup log often than hourly in fact. That's why we don't have to ask around how to recover db without losing data. |
|
|
|
jahnzee
Starting Member
4 Posts |
Posted - 2008-02-19 : 13:26:33
|
quote:
Originally posted by rmiao
Textbook is wrote by experienced people, we backup log often than hourly in fact. That's why we don't have to ask around how to recover db without losing data.
I didn't "ask around how to recover db without losing data." You would know this if you had carefully read my question, which was:Is it necessary to back up the transaction log in addition to the database, if the database is fully backed up and the log is backed up at about the same time?In other words, what is in the log backup, that is not in a full database backup, when both are backed up at the same time? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-19 : 13:31:07
|
| Its crazy to do full backup and log backup at same time. Schedule your log backup to occur every 15-20 minutes once full backup is done.Depends on the size of databases. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-19 : 13:33:10
|
| Delete old full backups in tape so that you can get spaces on tape. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-19 : 13:36:25
|
| what rmaio was saying to you was 100% right? It's not professional to argue with [b]SQL server Expert(MVP)[b]like rmaio. |
|
|
|
MuadDBA
628 Posts |
Posted - 2008-02-19 : 14:02:40
|
| jahnzee, you will find that you receive mush more hhelpful advice here when you are not rude to those who answer your questions.If you want to clear entries from the transaction log, and keep it small and manageable in size, you will want to do regular transactionlog backups, typically hourly as indicated. I suspect your transaction log file (.ldf) is much larger than it needs to be because you are not backing it up regularly. If your backup software is then no set up to exclude .mdf, .ndf, and .ldf files, you will find that the amoun of tape storage you need will continue to grow dramatically. |
|
|
|
jahnzee
Starting Member
4 Posts |
Posted - 2008-02-19 : 14:21:17
|
quote:
Originally posted by sodeep
Its crazy to do full backup and log backup at same time. Schedule your log backup to occur every 15-20 minutes once full backup is done.Depends on the size of databases.
Thank you, sodeep, that was what I was looking for. |
|
|
|
|
backing up log and database