May 14, 2007 at 5:06 pm
Why would you do differential backups instead of log backups? I have always done log backups on critical databases. But I was just reading on how differentials work with an extent flag so that only modified extents are backed up. But aren't log backups even more efficient (at least as far as space) because only the record/transaction involved is backed up?
Anyway, is there any reason that you would use differentials instead of log backups? (I only have 2000 at this point although I'll be working with 2005 soon.)
May 14, 2007 at 5:30 pm
Differential backups don't replace log backups, but they can allow faster recovery and limit the number of log backup files you must retain.
One possible plan would be weekly full backup on Sunday, differential backups Monday-Saturday, and log backups on the half hour. If you want to restore to Thursday at 2:15PM, you restore the weekly full backup, the Wednesday differential, and the log backups from the Wednesday differential to Thursday 2:30PM. Without the differential, you'd need to restore all the log backups from Sunday to Thursday.
Another scenario might be a highly critical system where you take daily full backups and log backups every 5 or 10 minutes. That adds up to 132 or 264 files per day. You could run a diff backup every two hours and then have at most 24 log restores in case of failure.
If a large portion of your database remains static, differential backups make a lot of sense. If the database is very dynamic, or you run an index optimization maintenance plan, the differential backup becomes as large as a full backup and is not a good idea.
May 15, 2007 at 3:34 pm
Thx! It finally makes sense.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply