How critical are transaction logs after a full backup of a SQL2005 database?

Question

How critical are transaction logs after a full backup of a SQL2005 database?

Example: I have a database called Test and I don't care about point in time recovery using transaction logs except that I might want to revert back to the database version taken at the time of the last FULL backup.

Now, in the backups directory I have a FULL backup called Test.bak plus 4 associated .trn files. If I make another new backup called Test1.bak is it safe for me to delete the Test.bak + the .trn files from the previous backup sequence? If I delete all backup files except for my Test1.bak, will I be able to recover from that one file only OR should I expect recovery problems because the .trn files are gone?

Answer 1

You can ignore the transaction log files for all intents and purposes, if you don't want to use them for log shipping or as part of your backup strategy.

Transaction log files serve two purposes, and by far the most important is to maintain data integrity in the event of a crash (and for short term transaction management). The second purpose involves backup, as is inferred by the question, and that purpose also facilitates log shipping and related things.

If you're not using transaction logs in a given database for anything other than its usual crash recovery/integrity behaviour, you might as well engage the Simple recovery mode, which in turn enables the "trunc. log on checkpoint" database option. When this happens, the transaction log can't be backed up, and it's truncated periodically. Instant hassle-free transaction logs!

Answer 2

You don't need transaction log backups at all if you're not interested in point in time recovery. You can always recover the database from the full database backup.

However, I cannot think of a good reason not to be interested in point-in-time recovery unless it is a dev or a test database (like yours).

Answer 3

If you don't use SIMPLE backup strategy, and you keep transaction logs, then when you do a FULL backup I believe makes the transaction logs up to that point no longer necessary. So, if the transaction logs are important to you and your backup strategy, you might want to use the COPYONLY flag during your backup and that way it wont interrupt the sequence of transaction log history.