August 3, 2009 at 12:24 pm
Hi,
I would like to know how long to store the transactional log backups. Would I need to store the ones older than the last full backup ? Currently we have full backups at the end of the day and we are storing 1 day's of trn log backup.
Thanks a lot !
August 3, 2009 at 1:00 pm
I think I store about 2 weeks for my production databases. If you only have 1 day, and find your last full backup had a problem, then you're stuck because you won't have all the transactions for the last 2 days. Or what if you discover data got deleted 3 days ago and you need to recover to a test database to that point in time, you're stuck. It's a balance of how much storage space you have available, and how important your data is.
August 3, 2009 at 1:15 pm
How many full and transaction log backup files you retain on disk really depends on your requirements for fulfilling restore requests and how much space you have for these files.
I keep about three weeks available for resolving possible production problems that occasionally occur. I haven't had to go back further than that, but we have files a=on tape should I have to do so. Three weeks works well for us and eliminates the need to involve Network Services should a database need a restore at this time.
August 3, 2009 at 1:31 pm
Would I need to store the ones older than the last full backup ?
No, once you no longer need the previous full backup the log files after that, prior to the next full backup, are no longer applicable. You would not be able to restore your database with just those log files without your previous full backup.
Example:
Sunday night full backup
Monday day - log backups
Monday night - full backup
Tuesday day - log backups
Tuesday mid-day database fails. I would need to restore the full from Monday night then the log backups in sequential order.
However as the others have posted, how long you keep the backups depends on your DR strategy and user/company requirements for data recovery.
But in restoring a database don't forget to get the tail-log backup prior to restoring: http://technet.microsoft.com/en-us/library/ms179314.aspx
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 3, 2009 at 1:41 pm
Thank you all for your views. They are of a great help to my understanding.
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply