July 25, 2008 at 7:36 am
we are in the market to upgrade our SQL backup infrastructure and talking to some vendors. yesterday one mentioned that they have customers who keep their log backups for months. we do so only for a few weeks because every weekend we run a full backup.
does anyone keep their log backups for a long time and why?
July 25, 2008 at 7:55 am
Keeping log backups and/or full backups for a long time allows you to revert much further back in time if something changed a long time ago, but you've only just realised it.
I've also heard of companies having a catastrophe, and only having a backup from a year ago - but a complete chain of tlogs to the present day.
Depending on what the data is, you may also have to keep it in some form for regulatory/legal reasons. In the UK, financial data has to be kept for 7 years I believe for tax purposes.
July 25, 2008 at 7:59 am
I keep mine on disk for a week but they are backed up to tape and archived daily, so if I need them I've got them. Tapes are reused after one year, so in theory, we do keep them a long time. However, I've really never needed to go back very far. If D/R warrants a restore, I'd want something very, very recent to minimize data loss. As long as your last full backup is good, and subsequent log backups are good as well, I'd think you'd be in good shape and wouldn't neccessarily need to retain much of the backups prior to that. One caveat, it depends entirely on YOUR business needs not your vendor's, nor my, recommendations.
Are these fine salespeople trying to sell you hardware to retain the backups on??? - Just my cynical little mind at work! 😀
-- You can't be late until you show up.
July 25, 2008 at 8:02 am
7 days of backups allows you to catch around 90% of all errors most of them due to human errors.
to get much better than that 21 days is the next big break an that takes you to around 98% of all errors should be trapped in that time frame.
Generally I recommend 7 days for most stuff and 21 days for extremely critical data sources that may have modification happen to them outside that magic week.
Cheers,
Wes
July 25, 2008 at 8:09 am
we have a 2 tiered policy. the master writable db's we send one full backup out of the month to offsite storage and i'm restoring a copy from 3 years ago right now. so that is OK.
the reporting db's we keep the data for 6 months
the way the vendor made it seem is that people keep log backups for months or years to be able to restore to a point in time. if we ever needed 3 year old data, we always restored from a full backup and that's it.
July 29, 2008 at 4:51 pm
if keeping log backups remember you also need the base backup(s) they are derived from
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 30, 2008 at 1:19 am
I generally prefer to keep two fulll backups (weekly), the diffs made against them (daily) and and of the log backups going back to the first of the fulls. That gives me a good number of options for recovery if necessary. Plus, for compliance reasons, the month end backups for 14 months are stored off-site.
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
July 31, 2008 at 11:40 am
The answer to your question of how long to keep backups is the standard SQL Server answer it depends. If your database contains financial data which eventually finds its way into financial statements and fillings with the FCC or if your company's stock is publicly traded, etc., etc. Your best answer would be to ask your company Financial VP or Treasurer for their guidance. I have worked at companies where detailed financial data is backed up and each backup up created at the end of a fiscal quarter is stored off site for seven years.
July 31, 2008 at 11:55 am
right now the chatter is to run weekly fulls and keep them for 6 weeks and the log backups also only for a few weeks. then run another full backup during the month and keep that one for 6 months.
after 6 months these are archived to secondary disk storage and the primary storage is continually reused
i still like tape backups over disk and this is worse than the old schedule of keeping every weekly full for 5-6 months.
July 31, 2008 at 12:57 pm
It depends, which isn't a great answer, but here's what I've done.
If I can afford the space, a weeks' worth of backups (full, diff, and logs).
Often space is an issue, so we've done 2 full backups and any diff/logs to support them.
August 1, 2008 at 5:45 am
This is totally dependend on your backup/restore and DR strategies. What's the SLA you have with your customers/users?
Having said that please keep at least the ones taken after your last full backup. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply