September 9, 2008 at 3:20 am
We have a new production server that is classed at business critical which we have just took over.
It already has backup jobs scheduled that they say fits there business needs.
As it is now our support I want what is best and not what fits just there needs.
Currently they have:
Daily 7pm Full backup
Daily between 7am - 6pm Differential backup
Daily at 12pm log backup
Great Full backup followed by the latest Differential backup.
But I dont get the log file backup? I cant see what point this plays if the system required restoring say at 3pm then what use would the transaction log be.
Would this be better with just full daily and then Differential every hr
Or full backup daily and a log backup every hr.
Space is not an issue.
SQL server 2k SP4
Your thoughts please.
September 9, 2008 at 6:43 am
Log backups should be more frequent than that. Every hour, every 30 min, every 15 min. The question to ask here is, should the drives of that server fail and all you have left are the backups (full, diff and log), how much data loss is considered acceptable?
When are the diffs run? Wasn't clear from what you said.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2008 at 6:47 am
You can run frequent diffs, but they'll get larger and larger. For that matter, why not run a full every hour? You could do that and it would let you recover to the hour. But you couldn't recover one minute later if someone deleted a table. Log backups help there.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 9, 2008 at 7:03 am
Thanks for your input,
Sorry, the differential backups are taken every hour during the day.
Which to me defeats the point of having a log backup once at 12:30pm
They expect to be able to recover to the last hour (diff backup)
Currently the database is 17Gig, and the differential backup can grow to about 2 gig.
As far as i see things, They have gone for hourly differential backups
So that in case of recovery they just restore the last full followed by a diff.
instead of a whole load of log files.
What would you consider best?
I am tempted to change the recovery mode to simple and not have log backups in this case.
September 9, 2008 at 7:06 am
I'd recommend full daily and log on the hour.
If you switch to simple and the DB crashes just before the diff, you've lost an hour's worth of data. If you're in full recovery, there's the possibility of being able to backup the tail of the log and hence lose no data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2008 at 8:30 am
Again thanks for your advice.
I have suggestered a change to Nightly Full backups followed by logs every 30mins.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply