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

Question

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

Example: I have a database called Test , and I don’t care that the time recovery time occurs using transaction logs, except that I can return to the version of the database that was accepted during the last FULL backup.

Now in the backup 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 + .trn files from the previous backup sequence? If I delete all backup files except my Test1.bak, can I restore only this file or should I expect to recover because the .trn files have disappeared?

Answer 1

You can ignore transaction log files for all purposes and tasks if you do not want to use them to deliver logs or as part of a backup strategy.

Transaction log files serve two purposes, and by far the most important is maintaining data integrity in the event of a failure (and for short-term transaction management). The second goal includes backing up, as defined in the question, and this goal also facilitates the delivery of magazines and related things.

If you do not use transaction logs in this database for anything other than the normal recovery / integrity behavior of disaster recovery, you can also use the simple recovery mode, which, in turn, allows you to use the database " trunc. log on checkpoint " . When this happens, the transaction log cannot be copied, and it is periodically truncated. Instant transaction logs no problem!

Answer 2

You do not need to back up transaction logs at all if you are not interested in recovering time. You can always restore a database from a full database backup.

However, I can’t come up with a good reason not to be interested in recovering at a specific point in time, unless it is a dev or test database (like yours).

Answer 3

If you do not use the SIMPLE backup strategy and save transaction logs, then when you make a FULL backup, I believe that transaction logs are no longer needed until now. Thus, if transaction logs are important to you and your backup strategy, you can use the COPYONLY flag during backups and this way it will not interrupt the sequence of the history of transaction logs.