May 5, 2008 at 6:00 am
Can anyone tell me a good reason to keep a production database in Full Recovery Mode if you are not doing transaction log backups?
My thoughts on the matter for most cases are:
1) You either keep it in Simple Recovery Mode and do full/differential backups at the appropriate intervals (usually daily), or
2) You keep it in Full Recovery Mode and in addition to the full/differential backups, do transaction log backups at the appropriate intervals (usually hourly)
I am wanting to standardize our SQL 2000/2005 backup & recovery process in our organization, and have come across some databases that are in FULL RECOVERY mode, but transaction logs are not being backed up. Instead, the transaction logs are truncated on a nightly basis.
I wanted to confirm my thoughts that this is not the way to go. However, I would like to hear any contrary views to any benefits of this method.
May 5, 2008 at 6:12 am
If you're not going to do tran log backups and you don't care about recovering to a point in time, in the case of a failure, then there's no reason to have the DB in full recovery.
If you're going to truncate the logs anyway, then leave the DB in Simple. Saves on admin.
Just bear in mind that you will only be able to restore the DB to the last full/diff backup if it's in simple. Make sure that potential data loss is acceptable.
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
May 5, 2008 at 6:51 am
Just bear in mind that you will only be able to restore the DB to the last full/diff backup if it's in simple. Make sure that potential data loss is acceptable.
Although if you are in full recovery mode, and you are not taking transaction log backups, you would potentially be in the same boat. At least you would need to assume that, correct?
Thanks for your input.
- John
May 5, 2008 at 7:15 am
Yup.
It's something for you to consider when deciding whether to switch to simple or enable log backups
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
May 5, 2008 at 10:01 am
Please ensure you check with management on data recovery and acceptable loss. If all data is bulk loaded from another source then you should use bulk logged or simple recovery model.
Sometimes point in time recovery is possilbe, even if not backing up the log file on a regular basis. The key being to perform an immediate backup of the current log file before your restoration process.
So yes, there is a reason to have the DB in full recovery mode, even if not backing up the transaction log. However, since your logs are being trucated on a nightly basis, then time of the last full backup in relation to the time of log truncation will determine if point in time recovery is truely an option.
Hope this helps
Marvin Dillard
Senior Consultant
Claraview Inc
May 6, 2008 at 8:05 am
Thanks Marvin. I appreciate your input.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply