November 1, 2007 at 10:31 am
One of my study books has the following example for a backup strategy for VLDBs. A filegroup backup each night a 7 p.m. (with the file1 being backed up on Monday, file2 on Tuesday, file3 on Wednesday, etc.) and then a *single* transaction log backup in the middle of the night at 1:00 a.m.
Correct me if I'm wrong, but as a strategy for a very large database, wouldn't you want your transaction log backups to occur more than once a day? If something screws the pooch in the middle of the day, I certain would want to be able to do a Point-in-time restore (if possible) and the transaction log must be HUGE at the end of the day if you're working with VLDBs. Right?
Thoughts, discussion, etc. appreciated. I've never worked with a VLDB before, so I'm unsure if the listed scenario would be appropriate IRL. Thanks in advance!
November 1, 2007 at 11:01 am
The only thing I can think of is that they are assuming it's a low-traffic DB - maybe a DW with a nightly population? Bulk-logged mode?
I know I'm working with a VLDB right now. It's a DW and speed / disk size is of highest importance so the DB is in Simple recovery mode. Obviously in that case we aren't doing t-log backups at all.
November 1, 2007 at 12:43 pm
The one time I did manage a VLDB, we sure has heck ran the log backups more frequently than once a day, but then it was an OLTP system. I'm working on small databases now, OLTP again, and we back up the logs pretty frequently. They've got to be making an assumption as to the type of system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 1, 2007 at 1:34 pm
Just sounds like a stupid example. If you are doing log backups, you want to do them often.
November 2, 2007 at 5:20 am
That's pretty much what I thought, but then I've missed obvious points from these book examples before, so I figured I'd double check.
Thanks everyone for your comments. I really appreciate them.
November 2, 2007 at 9:47 am
Of course - one part might be that if you're only backing up 1/7th of your database each day (which seems to be what the filegroup backup is proposing), I'm not sure what good the transaction log is going to do?
What happens when you replay logs from today against a database restored in this scenario?You'd need all logs from 7 days until now?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 2, 2007 at 10:12 am
Okay, now there's something I didn't consider at all. Because if you had to restore, you would have to restore every single last transaction log that goes along with all those days of filegroup backups, wouldn't you?
Would you restore them in order then? File1, TransLogMonday, File2, TransLogTuesday, etc.? Or how would the restore work for that?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply