Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm about to put my database in Full Recovery Model and start taking transaction log backups. I am taking a full nightly backup to another server and later in the evening this file and many others are backed up to tape.

My question is this. I will take hourly (or more if necessary) t-log backups and store them on the other server as well. However, if my full backups are passing DBCC and integrity checks, do I need to put my T-Logs on tape?

If someone wants point in time recovery to yesterday at 2pm, I would need the previous full backup and the transaction logs. However, other than that case, if I know my full back ups are good, is there value in keeping the previous day's transaction log backups?

share|improve this question

migrated from stackoverflow.com May 3 '10 at 20:20

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers 3

The old logs may be useful in certain scenarios, like if you discover a bad update operation occurred yesterday and you need to recover the data, you can use point in time recovery of an older full and older logs to recover to the moment when the bad data update occurred and copy out the deleted data. Basically anything that needs an image of the database prior to the last full.

Assuming you have 100% media confidence, ie. the last full backup is 100% guaranteed to be available and will restore, the log backups older than the most recent full are not needed. Whether to keep them longer or throw them away is going to be a decision driven not by restore requirements, but by history retention policy requirements and by the personal level of DBA (healthy) paranoia.

One thing to note is that log backups older than last full are useless without a prior full.

share|improve this answer
add comment

Personally, I prefer to take my data and transaction log backups to disk, and then replicate them to another set of disks (off-site in my case). It allows you to be very flexible in your backup and restore process.

Keeping old transaction logs is useful for Point In Time database recoveries. Depending on your business, it may be useful to restore the database back to a particular transaction from a few days ago (useful for tech support or development rollbacks).

I've used netbackup in the past to take transaction log backups direct to tape but found it incovienient to have to mess about with tapes to recover a backup (tapes are handled by a different team, in a store that is quite a long walk away from the server room). Tapes are also unreliable, especially when you REALLY need them.

Another problem with backups to tape is that if you do have a serious failure, then there will be a serious shortage of tape drives to allow you to get your backups back. There will be a whole host of other services and systems that will be on the list and your backups may not be at the top!

share|improve this answer
add comment

If someone wants point in time recovery to yesterday at 2pm, I would need the previous full backup and the transaction logs. However, other than that case, if I know my full back ups are good, is there value in keeping the previous day's transaction log backups?

You are correct the only purpose of the olkder transaction log would be to recover the older backup for whatever reason. The amount of time you keep the prior back depends entirely on how much confidence you have in the next backup. Personally I assume the next backup is going to fail and have at least the last backup available. I also don't get rid of them until I have to (eg with 15% reserve space I still have 400 GB of space so I might as well keep a backup or 2 there).

Don't forget Heisenberg's golden rule- your data doesn't exist until it exists in 2 places at all times.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.